Introduction to MIS Databases From ER Diagrams to Database Tables Mapping ER Diagrams to Tables 1
Designing the Tables The approach… Example data to be captured l Create ER Diagram. Each statement is a single path through the diagram l Tables are formed by traversing the diagram l 2
Example ER Diagram Fname Lname N Gender Salary Address WORKS_FOR 1 Start. Date Snn EMPLOYEE 1 MANAGES 1 CONTROLS Hours 1 SUPERVISION N N Locations DEPARTMENT Birthdate 1 Number Name WORKS_ON N PROJECT DEPENDENTS_OF Location Name N Number DEPENDENT Name Gender Birthdate 3
Review Constraints name ssn dname since lot Employees did Manages budget Departments Works_In since An employee can work in many departments; a dept can have many employees. In contrast, each dept has at most one manager. 4
Review Participation Constraints Does every employee work in a department? l If so, this is a participation constraint l Basically means “at least one” l since name ssn did lot Employees dname Manages budget Departments Works_In since 5
Identify Weak Entities A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. » Owner entity set and weak entity set must participate in a one-tomany relationship set (one owner, many weak entities). name ssn lot Employees cost Policy pname age Dependents 6
Review Relationships qty Parts Contract Departments VS. Suppliers Parts can-supply needs Suppliers Departments deals-with S “can-supply” P, D “needs” P, and D “deals-with” S does not imply that D has agreed to buy P from S. 7
How do you make the Tables? Fname Lname N Gender Salary Address WORKS_FOR 1 Start. Date Snn EMPLOYEE 1 MANAGES 1 CONTROLS Hours 1 SUPERVISION N N Locations DEPARTMENT Birthdate 1 Number Name WORKS_ON N PROJECT DEPENDENTS_OF Location Name N Number DEPENDENT Name Gender Birthdate 8
Each Entity becomes a Table ENTITY TABLE Employee Department Project PRIMARY KEY Employee_SSN Department_Number Project Number 9
1: N Relationships - Create the Primary Key For each 1: N relationship – create the Primary Key from the entity on the 1 side to the entity on the N side where it will become a foreign key. EMPLOYEE N WORKS_FOR DEPARTMENT 1 Department Number is moved into the Employee entity 10
Weak Entities Become Tables as Well For each weak entity create a table - include the Primary Key of the owner tables The Primary Key becomes the: owner key plus the weak entity key Entity Table Primary Key Dependent Employee_SSN + Dependent_No 11
What about M: N Relationships? For each M: N relationship create a new table with the Primary Key being the Primary Key of both entities involved in the relationship EMPLOYEE WORKS_ON Empno PROJECT Hours E 1 E 2 E 3 P 1 P 2 Projno 3 4 5 3 12
Representing the M: N Relationships 13
The Finished Table Design EMPLOYEE Fname Lname Essn Bdate Address M/F Salary Superssn Dno p. k DEPARTMENT f. k Dname Dnumber Mgrssn Mgrstartdate p. k DEPT_LOCATIONS Dnumber Dlocation WORKS_ON f. k Essn NOT NULL f. k Pnumber NOT NULL PROJECT Pname Pnumber Hours f. k Essn Plocation Dnumber DEPENDENT Dependent_name M/F Bdate 14
Скачать презентацию Introduction to MIS Databases From ER Diagrams to
41d0277c84c989b3e62b016c5f75f22d.ppt