Database Design

1. Database Design

2. Security Design miniworld Requirements & collection analysis refinement Database Requirements Conceptual Design Conceptual Schema (in a high-level data model) DBMS independent Data Model Mapping DBMS specific Conceptual Schema (in the model of a specific DBMS) Physical Design Internal Schema (in the model of a specific DBMS)

3. Entity-Relationship (ER) model is a high level conceptual data model. • Entity:Real-world object distinguishable from other objects. • Attribute: A property of an entity. Number=2311 Street=Kirby Apt.=Null Street Address City=Houston State=Texas Zip=77546 Name=John Smith Address BirthDate=3/17/1936 Age=55 HomePhone=Null Degree={B.S., M.S., Ph.D.} (composite attribute) (derived attribute, deliverable from the BirthDate) e1 (Multivalued attribute)

4. Types of Attributes • Simple attribute is not divisible (E.g., age). • Composite attribute can be divided into smaller subparts (e.g., Date (Month, Day, Year)). • Complex attribute:Composite and multivalued attributes can be nested and form a complex attribute. • CAR(Registration(RegistrationNumber, State), VehicleID, Make, Model, Year, {color}). • Car1=((ABC123,TX),TK629,Saturn, SC1, 1993, {red,black}) Meaning of Null value: unknown or not applicable

5. Entity Set (Entity Type):A collection of similar entities. E.g., all employees. • Key Attribute: An attribute whose values are distinct for each individual entity. • Domain:A set of all possible values for an attribute. • Example: The domain of Age attribute of entity type EMPLOYEE can be the set of integer numbers between 16 and 70. name cid addr name ssn Course student • Notation: entity set, attribute, key attribute

6. Relationship: Association among two or more entities. • Example: Attishoo enrolled in COMS461 course • Relationship Set: Collection of similar relationships. • A relation may also have some attributes to describe the properties of this relation name semester addr cid name ssn Course student enrolled entity set relationship set • Notation: relationship set, attribute

7. Same entity set could participate in different relationship sets, or in different “roles” in same set. • A relationship set can involve two or more entity sets name semester addr cid name ssn course student enrolled entity set Advised-by duration semester teach instructor relationship set ssn name addr

8. Structural Constraints • Cardinality Ratio Constraint specifies the number of relationship instances that an entity can participate in. 1-to-1 1-to Many Many-to-1 Many-to-Many

9. since name dname ssn lot Employees Manages did budget Departments • Key constraint specifies that each entity of an entity set can participate in at most one relationship in a relationship set. Each department has at most one manager. • Participation Constraint: • Total participation:Every entity in the entity set participates in the relationship set. • E.g., Every department must have a manager. • ER-Notation: • Partial participation

10. E-R Model Exercises

11. Exercise • A company has a number of employees. The attributes of EMPLOYEE include Employee_ID (identifier), Name, Address, and Birthdate. • The company also has several projects. Attributes of PROJECT include Project_ID (identifier), Project_Name, and Start_Date. • Each Employee may be assigned to one or more projects, or may not be assigned to a project. • A project must have at least one employee assigned, and may have any number of employees assigned. • An employee’s billing rate may vary by project, and the company wishes to record the applicable billing rate (Billing_Rate) for each employee when assigned to a particular project.

12. Exercise • A university has a large number of courses in its catalog. Attributes of COURSE include Course_number (identifier), Course_name, and Units. • Each course may have one or more different courses as prerequisites, or may have no prerequisites. Similarly, a particular course may be a prerequisite for any number of courses, or may not be prerequisite for any other course.

13. Exercise • A hospital has a large number of registered physicians and patients. • Attributes of PHYSICIAN include Physician_ID (identifier) and Specialty. • Attributes of Patients include Patient_ID (identifier) and Patient_Name. • Any patient who is admitted must have exactly one admitting physician. A physician may optionally admit any number of patients. • Once admitted, a given patient must be treated by at least one physician. A particular physician may treat any number of patients, or may not treat any patients. • Whenever a patient is treated by a physician, the hospital wishes to record the details of the treatment (Treatment_Detail). Components of Treatment_Detail include Date, Time, and Results.

14. Weak Entities • A weak entitycan be identified uniquely only by considering some of its attributes and the primary key of another (owner) entity. • Weak entity set must have total participation in its identifyingrelationship set. • Weak entity set must have key constraint. name cost pname age ssn lot Policy Dependents Employees Weak entity set Owner entity set Identifying relationship set

15. name ssn lot Employees hourly_wages hours_worked ISA contractid Contract_Emps Hourly_Emps ISA (`is a’) Hierarchies • If we declare A ISA B, every A entity is also considered to be a B entity. • Reasons for using ISA: • To add attributes specific to a subclass. • To identify entities that participate in a relationship. • Overlap constraints: • Can Joe be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/disallowed) • Default value: no overlap; • Otherwise, write Hourly_Emps OVERLAPS Contract_emps • Covering constraints: • Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity? (Yes/no) • Default value: no; • Otherwise write Hourly_Emps and Contract_Emps COVER Employees

16. ER design is subjective. There are often many ways to model a given scenario! • Analyzing alternatives can be tricky, especially for a large enterprise. Common choices include: • Entity vs. attribute, entity vs. relationship, binary or n-ary relationship, whether or not to use ISA hierarchies, and whether or not to use aggregation. • ER diagrams can use different notations, but use the same concept. • Several software tools are available for creating ER diagrams: IBM Rational Rose, Microsoft Visio

17. Questions to think • Is there anything that cannot be described by ER model?

18. Key Concepts of ER Model • Entity set • Relationship set • Constrains • Key constrain • Total participation constrain • Weak entity set • ISA

19. Database Requirements: • Maintain the following information about the employees of the company. • [Social security number, date of birth, name, gender, address, salary] • Users want to be able to perform a search on either first name, last name, or middle name. • An employee can have at most one supervisor who is also an employee, but a supervisor can have more than one supervisees. • An employee can have dependents. The dependent name, gender, and date of birth, and the relationship of the employee and his/her dependent are needed in the database. • The company is divided into departments. For each department, the department name, department number, locations are needed in the database. A department can locate in several locations. • A department can control several projects. Each project must be controlled by only one department. The name of the project, the project number, and its location need to be stored in the database. • Each employee works on at least one project and each project must have at least one employee. The number of hours per week that an employee works on a project must be recorded. • Each employee must work for only one department and the department must have at least one employee. Users also want to query a number of employees in a department. • Each department must be managed by one employee. An employee can be a manager of only one department. • The date when the employee becomes a manager for the department needs to be maintained.

20. Database Requirements: • Maintain the following information for real estate firm • The firm has a number of sales offices in several states. Attributes of sales offices include Office Number (identifier) and Location. • Each sales office is assigned one or more employees. Attributes of employee include Employee ID (identifier) and Employee Name. An employee must be assigned to only one sales office. • For each sales office, there is always one employee assigned to manage that office. • The firm lists property for sale. Attributes of property include Property ID (identifier) and Location. Components of Location include Address, city, State, and Zip Code. • Each unit of property must be listed with one (and only one) of the sales offices. A sales office may have any number of properties listed or may have no properties listed. • Each unit of property has one ore more owners. Attributes of owners are Owner ID (identifier) and Owner Name. An owner may own one or more units of property. An attribute of the relationship between property and owner is Percent Owned.