Introduction to Database Design 1 Database Design Process

Introduction to Database Design 1

Database Design Process l l Requirement analysis Conceptual database design l l Logical database design l l To develop a description of the data using a high-level data model such as the ER (Entity-Relationship) model. To convert the conceptual database design into a database schema in the chosen data model (e. g. , converting an ER schema into a relational schema). Schema refinement l Normal forms l Indexing, clustering, and tuning Physical database design Application and security design Introduction to Database Design 2

Conceptual Design l A database schema can be represented in an ER diagram. l l l What are the entities and relationships in the enterprise? What information about these entities and relationships should we store in the database? What are the integrity constraints or business rules that hold? Introduction to Database Design 3

ER Model l l Entity l Real-world object distinguishable from other objects. l An entity is described (in DB) using a set of attributes. ssn Entity Set l A collection of similar entities. l All entities in an entity set have the same set of attributes. l Each attribute has a domain. l Each entity set has a key. l name lot Employees A key is a minimal set of attributes whose values uniquely identify an entity in the set. Introduction to Database Design 4

ER Model (cont. ) l Relationship l l Association among two or more entities. Relationship Set l l Collection of similar relationships. Descriptive attributes are used to record information about the relationship. name ssn lot Employees Introduction to Database Design dname since did Works_in budget Departments 5

Instances l l An instance of a relationship set is a set of relationships. A snapshot of the relationship set at some instant in time. Employees Introduction to Database Design Works_in Departments 6

name More Examples l l ssn lot Employees An entity set can participate in different “roles” in the same relationship set. supervisor subordinate A ternary relationship set relates 3 entity sets. Reports_to name since ssn lot Employees Introduction to Database Design dname address did Works_in 2 Locations budget Departments capacity 7

Key Constraints l l l Each department has at most one manager. Each department entity appears in at most one Manages relationship. The arrow indicates that a department entity can uniquely determine the Manages relationship in which it appears. name ssn since lot Employees Introduction to Database Design dname did Manages budget Departments 8

Participation Constraints l l l Total participation: Every entity must appear in an instance of the relationship. It is represented by a thick line in the ER diagram. Partial participation: A participation that is not total. In the following example, each department has at least one manager but only some of the employees are managers. name ssn since lot Employees Introduction to Database Design dname did Manages budget Departments 9

One-to-many Relationship l One employee can be associated with many departments, whereas each department can be associated with at most one employee as its manager. Employees Partial participation Introduction to Database Design Manages One to Many Departments Total participation 10

Connectivity of Relationships l One-to-one l l One-to-many l l The chairs relationship between Teachers and Departments The teaches relationship between Teachers and Courses Many-to-many l The enrolled-in relationship between Students and Courses Introduction to Database Design 11

Weak Entities l l l 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). Weak entity set must have total participation in this identifying relationship set. name ssn cost lot Employees Introduction to Database Design pname Policy birthday Dependents 12

Design Choices l l Should a concept be modeled as an entity or an attribute? Should a concept be modeled as an entity or a relationship? Identifying relationships: Binary or ternary? Constraints in the ER Model: l l A lot of data semantics can (and should) be captured. But some constraints cannot be captured in ER diagrams. Introduction to Database Design 13

Entity vs. Attribute l l Should address be an attribute of Employees or an entity (connected to Employees by a relationship)? Depends upon the use we want to make of address information, and the semantics of the data: l l If we have several addresses per employee, address must be an entity (since attributes cannot be setvalued). If the structure (city, street, etc. ) is important, e. g. , we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic). Introduction to Database Design 14

Entity vs. Attribute (Cont. ) l Works_in 4 does not allow an employee to work in a department for two or more periods. name ssn from lot Employees Introduction to Database Design to dname did Works_in 4 budget Departments 15

Entity vs. Attribute (Cont. ) l We introduce a new entity set, Duration, to record several values of the descriptive attributes for each instance of this relationship. name ssn dname lot Employees Works_in 4 from Introduction to Database Design did Duration budget Departments to 16

Binary vs. Ternary Relationships l l The following ER diagram models a situation in which an employee can own several policies, each policy can be owned by several employees, and each dependent can be covered by several policies. If each policy is owned by just one employee, and each dependent is tied to the covering policy, this diagram is inaccurate. cost policyid name ssn Policies lot Employees Introduction to Database Design pname Covers birthday Dependents 17

Binary vs. Ternary Relationships (Cont. ) l If each policy is owned by just one employee, and each dependent is tied to the covering policy, this diagram is a better design. pname ssn Dependents cost policyid Policies birthday Beneficiary lot Employees Introduction to Database Design Purchaser 18