Part 7 Logical Data StructuresCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 2 Logical Database Design • Constructive approach • Considers semantics • Documents • data dependencies • identifiers • entities • needed relations • “rules”Copyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 3 Logical Data Structures (LDS) Graphical means of • naming and • depicting the types of data in a database Simple, yet precise Useful to • technically oriented analysts • application-oriented users Easy to read Supports the design task • logical structure design is hard • tool aids the design task • notation does not get in the wayCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 4 Basic LDS Components Entity • any type of thing about which information is maintained entity_nameEXAMPLEstudent Attribute • a characteristic of exactly one entity (fully functionally dependent on the entity) attribute_name EXAMPLE: Student attributesstudentstudent_namestudent_id#soc_sec# Relationships • an association between a pair of entities (or “roles”), one-to-one, one-to-many only orbutneverCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 5 Example Relationships 1 - 1 Example: Monogamous marriage manmanwomanwomanCan label relationshipwife of man/husband of woman 1-M Example: Students of a college collegestudent • Need not label a relationship if it can be stated as: college of student / students of college or student has college / college has studentsCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 6 Handling an M-M Relationship M-M Example: Brother - Sister man_namewoman_namemanwomansisters of man/brothers of woman Problem: how do you represent the presence of sibling rivalry? THIS WON'T WORK man_namewoman_namemanwomanrivalry SOLUTION man_namewoman_namemanwomanbrother-sisterrivalryCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 7 Identifier Representation Identifier: a set of attributes or relationships that uniquely identify an instance of an entity (single field key) (multiple-field key) Example: college_namecollege#college studentstudent_namestudent_id#soc_sec#Copyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 8 Primary Key / Candidate Key state_namestatestate_abbrevcitycity_namecity#primary keycandidate keyCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 9 Sample Database Employee: (emp) attributes: Ename, Job, Mgr, Hired, Rate, Bonus Department: (dept) attributes: DeptNo, Dname, Loc, Dbudget Task: (task) attributes: Tname, Hours Project: (proj) attributes: Project_id, Description, Pbudget, Due_date Relationships • employees are members of a department • employees have a manager who is an employee • employees are assigned to tasks on projectsCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 10 LDS for Sample Database DeptNoDnameLocDbudgetdeptEnameJobHiredRateBonusMgr TnameHourstaskempProject_id DescriptionPbudgetDue_dateprojCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 11 Functional Dependency Revisited DeptNoDnameLocDbudgetdeptempEname JobRate DeptNo identifies dept instances DeptNo --> Dbudget Dbudget is fully functionally dependent on DeptNo DeptNo --> Loc Loc is fully functionally dependent on DeptNo Dname is an alternate key Dname --> Dbudget Dbudget is fully functionally dependent on Dname Ename identifies emp instances Ename --> Job Job is fully functionally dependent on Ename Ename --> Rate Rate is fully functionally dependent on Ename an employee instance determines exactly one department Ename --> DeptNo DeptNo is fully functionally dependent on Ename Ename --> Loc Loc is fully functionally dependent on Ename, but this is a transitive full functional dependenceCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 12 LDS for Example 1 - Suppliers A supplier supplies many parts, and a part can be supplied by many suppliers suppliersupppart_typepartavailabilityCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 13 LDS for Example 2 - Inventory A product can be stored in many warehouses and a warehouse can contain many products part#productwarehousewarehouse#inventorywh_addressquantityCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 14 LDS for Example 3 - Departments A department can have many employees, and employee can only be in one department deptdept_locdepartmentemployeenameCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 15 LDS for Example 4 - Locations Departments have one number, one name, and one location dept#dept_namedepartmentdept_locCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 16 LDS for Example 5 - Stock An inventory is comprised of combinations of various parts from various suppliers - a supplier can supply many parts, and a part can be supplied by many suppliers p#partsuppliers#inventorysnameqtyCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 17 LDS for Example 6 - Enrollment A student can take many subjects; a subject can be taken by many students. A subject can be taught by many teachers, a teacher can teach only one subject. A student can be taught by many teachers, a teacher can teach many students. studentstusubjsubjectregistrationteachteacherCopyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 18 LDS for EXAMPLE 7 - SKILLS Employees can have many skills, and a skill can be had by many employees; an employee can know many languages and a language can be known by many employees. employeeempjob_skillemp/lang/job_skillskilllanglanguageThis diagram is correctif all 3 are interdependent employeeempjob_skillskilllanglanguageThis diagram is almost never correct(It implies that a skill can be held by only one employee)Copyright  1971-2002 Thomas P. Sturm Logical Data Structures Part 7, Page 19 CORRECT LDS for INDEPENDENCE Assuming job skills and language skills are independent, they represent two separate many-to-many relationships