CS 3630 Database Design and Implementation

1. CS 3630 Database Design and Implementation

2. Relational Database A number of tables. Normalized! PK and FK to keep relationships.

3. Candidate Key A minimum set of attributes that uniquely identifies each record of a table. No proper subset satisfying the definition. R (Att1, Att2, Att3) Candidate Keys: (Att1, Att2) Either Att1 or Att2 is not a candidate key But Att3 could be a candidate key

4. Candidate Key A minimum set of attributes that uniquely identifies each record of a table. No proper subset satisfying the definition. R (Att1, Att2, Att3) Candidate Keys: (Att1, Att2) Functional Dependency Att1, Att2  All other attributes Att1  All other attributes Not True Att2  All other attributes Not True

5. Candidate Key, PK and AK • A table could have more than one candidate key. • One candidate key is selected as the primary key (PK). • Other candidate keys become alternate keys (AK). • PK can be copied to other tables as foreign keys (FK).

6. Table Table Schema Definition DBDL All possible records Table Instance Records stored in database Different at different times PK and FK

7. Example Schedule (staffNo, dentistName, patNo, patName, surgeryNo, date, time) PK: surgeryNo, date, time AK: staffNo, date, time patNo, date, time FK: surgeryNo

8. Design Methodology A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. Three main phases • Conceptual database design E-R Model • Logical database design Mapping E-R Model to (relational) database schema Normalization • Physical database design

9. E-R Model Entity Attributes: Simple or Composite Single-value or Multi-value Relationship One-to-One One-to-Many Many-to-Many Could have attributes

10. Multiplicity: One-to-One Staff (1..1) Manages (0..1) Branch Each staff manages zero or one branch. Each branch has exactly one manager. For one object on the other side! E1 (0..1) IsRelatedTo (0..1) E2

11. Multiplicity: One-to-Many Staff (0..1) Oversees (0..*) Property Each property is overseen by zero or one staff. Each staff oversees zero or more properties. For one object on the other side! Parent table: Staff E1 (0..*) IsRelatedTo (0..1) E2 Parent table: E2

12. Multiplicity: Many-to-Many Student (0..*) Takes (1..*) Course Each course is taken by zero or more students. Each student takes one or more courses. For one object on the other side! E1 (0..*) IsRelatedTo (1..*) E2

13. Design Methodology A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. Three main phases • Conceptual database design E-R (EER) Model • Logical database design Mapping E-R Model to (relational) database schema Normalization • Physical database design

14. Mapping Entity Composite Attribute Staff staffNo Name: Composite (firstName, lastName) . . . Table Schema Staff (staffNo, firstName, lastName . . .)

15. Mapping Entity Multi-value Attribute Student studentNo Name: Composite (first, last) CoursesTaken: Multi-Value . . . Table Schema Student (studentNo, firstName, lastName . . .) AcademicProg(studentNo, CourseID) (one student will have multiple records in the table)

16. Mapping Relationships • One-to-Many Copy PK of parent table into child table as FK • One-to-One Copy PK of one table into another table as FK • Many-to-Many Create a new table

17. Design Methodology A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. Three main phases • Conceptual database design E-R (EER) Model • Logical database design Mapping E-R Model to (relational) database schema Normalization • Physical database design

18. Functional Dependency Definition In a relation R S: a set of attributes T: a set of attributes S  T For ANY relation instance If two records of R have the same values on S Then they will have the same values on T Foundation for Normalization. Trivial FDs A  A A, B, C  B, C

19. First Normal Form (1NF) • No multi-value attributes • Done when mapping E-R model to relational schema

20. Second Normal Form (2NF) A relation R is in 1NF, and every non-primary-key attribute is fully functionally dependent on the primary key Then R is in 2NF.

21. Third Normal Form (3NF) Relation R in 2NF, and No non-Primary-Key attribute is transitively functionally dependent on the primary key Then R is in 3NF. Must be in 2NF!

22. Boyce-Codd Normal Form (BCNF) Definition R in 1NF and Every determinant (the left side of a FD) is a candidate key. Stronger than 3NF!

23. Relational Algebra Operations • Selection • Projection • (Union) • (Set difference) • (Intersection) • Cartesian product • Join Natural Join Outer Join

24. Relational Algebra Operations • Apply to relation/table instances • The result is still a relation/table instance • Syntax

25. Natural Join R: Att1 Att2 Att3 S: Att2 Att4 100 10 x 20 a 90 20 y 15 b 101 15 z Common attribute Att2 R S =  (Att1, R.Att2, Att3, Att4) (R.Att2 = S.Att2) (R S)   Att1 R.Att2 Att3 S.Att2 Att4 100 10 x 20 a 100 10 x 15 b 90 20 y 20 a 90 20 y 15 b 101 15 z 20 a 101 15 z 15 b  Att1 R.Att2 Att3 S.Att2 Att4 90 20 y 20 a 101 15 z 15 b  Att1 R.Att2 Att3 Att4 90 20 y a 101 15 z b 

26. Database Queries • Which tables? • Which operations? • Which common attribute for join? • Which attributes to retrieve?

27. Table Schemas Staff (staffNo, dentistName, phone) Patient (patNo, firstName, lastName, DOB, phone) Room (roomNo, size, spepcialEquip) Schedule (patNo, date, time, staffNo) Allocation (staffNo, date, roomNo) New Table: Room spepcialEquip: bool

28. List all patients who had or will have a surgery in a surgery room with special equipment at the age of 20 or younger. Assume two functions: CurrentDate and DifferenceInYear that takes two dates and returns the difference in years of the two dates. Patient (patNo, firstName, lastName, DOB, phone) Room (roomNo, size, spepcialEquip) Schedule (patNo, date, time, staffNo) Allocation (staffNo, date, roomNo)  Patient.* (specialEquip = ‘true’ And DifferenceInYear(CurrentDate, DOB) <= 20 ( Room Allocation Schedule Patient ) ) A patient may have multiple such surgeries, but will be in the result only once.

29. Produce a status report on all patients Each patient Show patNo, firstName, lastName, DOB If the patient had or will have surgeries with special equipment at age 20 or younger, also show the date the surgery was/is done and staffNo, dentistName of the dentist who did or will do the surgery. (Otherwise, use null for staffNo and dentistName) Staff (staffNo, dentistName, phone) Patient (patNo, firstName, lastName, DOB, phone) Room (roomNo, size, spepcialEquip) Schedule (patNo, date, time, staffNo) Allocation (staffNo, date, roomNo)  Patient.patNo,firstName, lastName, DOB, Staff.staffNo, dentistName (specialEquip = ‘true’ And DifferenceInYear(CurrentDate, DOB) <= 20 (Patient (Room Allocation Schedule Staff ) ) )  Patient.patNo,firstName, lastName, DOB, Staff.staffNo, dentistName (Patient (specialEquip = ‘true’ And DifferenceInYear(CurrentDate, DOB) <= 20 ( (Room Allocation Schedule Staff ) ) ) A patient may have multiple such surgeries, and will be in the result multiple times.

30. Test 1 • Friday • Lab 206 • Sets and Mapping • Relational Database Model • Relational Algebra • Mapping E-R model to table schema • Functional Dependency • Normalization