Database Concepts and SQL Fundamentals

Posted on Jan 15, 2025 in Language

Database Concepts: True or False Statements

  • Primary Key Indexing: True. There is no need to index the primary key as it is automatically indexed.
  • Unique Constraint Names: True. All constraints within a schema must be given unique names.
  • Weak Entity Type Definition: True. A weak entity type is an entity type that is existence-dependent on some other entity type.
  • One-to-Many and Many-to-One Relationships: False. One-to-many and many-to-one relationship types in the reduction process can be represented by adding the primary key in the entity type representing the “one side” as a foreign key in the entity type that represents the “many side”.
  • Specialization/Generalization Hierarchy: False. In the specialization/generalization hierarchy, the superclass/subclass relationship is one-to-one (1:1).
  • Disjoint Constraint: False. The disjoint constraint determines whether a member of a subclass can belong to more than one subclass. The completeness constraint is used to determine whether every member in a superclass must participate as a member of a subclass.
  • Normalization and Joins: True. Generally, in the normalization process, the higher the normal form, the more joins are required.
  • 3NF and BCNF: False. Every table in Third Normal Form (3NF) is not also in Boyce-Codd Normal Form (BCNF).
  • Referential Integrity and Self-Referencing: True. It is possible for the referencing and referenced relations in the referential integrity constraint to refer to the same relation.
  • Nulls in Composite Primary Key: False. If the primary key has several attributes, null is not allowed in any of these attributes.
  • Natural Join and Attribute Names: True. The NATURAL JOIN operation in SQL requires the join attributes to have the same name.
  • NOT EXISTS Behavior: False. NOT EXISTS (Q) returns True if there are no tuples in the result of the nested query Q.
  • Weak Entity Type Definition: True. A weak entity type is an entity type that is existence-dependent on some other entity type.
  • One-to-Many and Many-to-One Relationships: False. One-to-many and many-to-one relationship types in the reduction process can be represented by adding the primary key in the entity type representing the “one side” as a foreign key in the entity type that represents the “many side”.
  • Normalization and Joins: True. Generally, in the normalization process, the higher the normal form, the more joins are required.
  • BCNF and 3NF: True. A table in Boyce-Codd Normal Form (BCNF) is also in Third Normal Form (3NF).

SQL Grant and View Examples

(a)

GRANT SELECT, UPDATE 
ON EMPLOYEE, DEPARTMENT, DEPT_LOCATIONS, PROJECT, WORKS_ON 
TO USER_A 
WITH GRANT OPTION;

(b)

CREATE VIEW EMPS AS 
SELECT FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, 
SUPERSSN, DNO 
FROM EMPLOYEE; 
GRANT SELECT ON EMPS 
TO USER_B; 
CREATE VIEW DEPTS AS 
SELECT DNAME, DNUMBER FROM DEPARTMENT; 
GRANT SELECT ON DEPTS TO USER_B;

(c)

GRANT SELECT, UPDATE ON WORKS_ON TO USER_C; 
CREATE VIEW EMP1 AS 
SELECT FNAME, MINIT, LNAME, SSN 
FROM EMPLOYEE; 
GRANT SELECT ON EMP1 
TO USER_C; 
CREATE VIEW PROJ1 AS 
SELECT PNAME, PNUMBER 
FROM PROJECT; 
GRANT SELECT ON PROJ1 TO USER_C;

(d)

GRANT SELECT ON EMPLOYEE, DEPENDENT TO USER_D; 
GRANT UPDATE ON DEPENDENT TO USER_D;

(e)

CREATE VIEW DNO3_EMPLOYEES AS 
SELECT * FROM EMPLOYEE WHERE DNO=3; 
GRANT SELECT ON DNO3_EMPLOYEES TO USER_E;

Book and Author Relationship

  • Every book is written by one author.
  • Every author writes one or more books.

Department and Employee Schema

Department(dept_id, name, phone, mgr_id)

Employee(emp_id, fname, lname, phone, address, dept_id)

Staff, Supervisor, Manager, and Branch Schema

Staff(staffNo, name, position, salary, superNo, branchNo)

Supervisor(staffNo, rank)

Manager(staffNo, mgrStartDate, bonus)

Branch(branchNo, street, city, postcode, mgrNo)

Partial and Transitive Dependencies

  • C4 -> C1 is a partial dependency since part of the primary key (C4) determines a non-key attribute (C1).
  • C2 -> C3 is a transitive dependency since a non-key attribute (C2) determines another non-key attribute (C3).