Introduction to MIS Databases

1. Introduction to MIS Databases From ER Diagrams to Database Tables Mapping ER Diagrams to Tables

2. Designing the Tables The approach… • Example data to be captured • Create ER Diagram. Each statement is a single path through the diagram • Tables are formed by traversing the diagram

3. Example ER Diagram Fname Lname Number Name N Gender 1 Locations Salary WORKS_FOR Name Address StartDate DEPARTMENT Snn EMPLOYEE 1 1 MANAGES Birthdate CONTROLS Hours N N 1 WORKS_ON 1 N PROJECT SUPERVISION DEPENDENTS_OF Location Name N Number DEPENDENT Name Gender Birthdate

4. since name dname ssn lot Employees Manages since Review Constraints An employee can work in many departments; a dept can have many employees. In contrast, each dept has at most one manager. did budget Departments Works_In

5. Review Participation Constraints • Does every employee work in a department? • If so, this is a participation constraint • Basically means “at least one” since since name name dname dname ssn did did budget budget lot Departments Employees Manages Works_In since

6. name cost pname age ssn lot Policy Dependents Employees 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-to-many relationship set (one owner, many weak entities).

7. Review Relationships qty S “can-supply” P, D “needs” P, and D “deals-with” S does not imply that D has agreed to buy P from S. Departments Parts Contract VS. Suppliers Parts Departments needs can-supply deals-with Suppliers

8. How do you make the Tables? Fname Lname Number Name N Gender 1 Locations Salary WORKS_FOR Name Address StartDate DEPARTMENT Snn EMPLOYEE 1 1 MANAGES Birthdate CONTROLS Hours N N 1 WORKS_ON 1 N PROJECT SUPERVISION DEPENDENTS_OF Location Name N Number DEPENDENT Name Gender Birthdate

9. Each Entity becomes a Table ENTITY TABLE PRIMARY KEY Employee Employee Employee_SSN Department Department Department_Number Project Project Project Number

10. 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 WORKS_FOR DEPARTMENT N1 Department Number is moved into the Employee entity

11. 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 Dependent Employee_SSN + Dependent_No

12. What about M:N Relationships? For each M:N relationship create a new table with the Primary Key being the the Primary Key of both entities involved in the relationship EMPLOYEE WORKS_ON PROJECT Empno Hours Projno E1 P1 3 E1 P2 4 E2 P1 5 E3 P2 3

13. Representing the M:N Relationships

14. 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 PROJECT WORKS_ON Pname Pnumber Plocation Dnumber f.k f.k EssnPnumber Hours DEPENDENT f.k NOT NULL NOT NULL EssnDependent_name M/F Bdate