Database Design: Conceptual Modeling with ER Model CENG 351
Database Design Process • Step 1: Conceptual database design – High level description of the data and the constraint – This step can use ER or similar high level models since name ssn dname lot Employees budget did Works_In Departments • Step 2: Logical database design – Convert database design into a database schema CREATE TABLE Employees (ssn CHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY (ssn)) CENG 351
Database Design Process (cont. ) • Step 3: Schema refinement • Step 4: Physical database design – Data storage, indexing • Step 5: Access Control – Identify which users can access which par of the data CENG 351
ER Model Basics: Entity set ssn name lot Employees • Entity: Real-world object distinguishable from other objects. – An entity is described (in DB) using a set of attributes. • Entity Set: A collection of similar entities. E. g. , all employees. – – All entities in an entity set have the same set of attributes. Each entity set has a key that uniquely identifies it. Each attribute has a domain. Entity set can be mapped to a relation easily. CENG 351
ER Model Basics: Relationship set lot Employees ssn since name ssn name dname Employees budget did Works_In lot Departments supervisor subordinate Reports_To • Relationship: Association among 2 or more entities. E. g. , Steve Jobs works in Grocery department. • Relationship Set: Collection of similar relationships. • Same entity set could participate in different relationship sets, or in different “roles” in the same set. CENG 351
ER Model Basics: Relationship Set • Relationship sets can also have descriptive attributes (e. g. , the since attribute of Works_In). For a given entity pair we cannot have more than one associated descriptive attribute value. E. g. , an employee-department pair can have one since attribute value. • A relationship must be uniquely identified by the entities taking part. • Thus, in translating a relationship set to a relation, attributes of the relation must include: – Keys for each participating entity set (as foreign keys): This set of attributes (individual keys) forms superkey for the new relation. – All descriptive attributes of the relationship. CENG 351
ER Model Basics: Relationship Set • An instance of a relationship set is a set of relationships • An example instance may have an employee working in none, one or many departments. • Ternary Relationship: A relationship having three entities involved. – E. g. , Works_In can have a Locations entity with possible attributes. taking part as well, where a department may have offices in different cites. CENG 351
Additional features • One would need additional features for the ER diagrams to represent some important properties of entities and their relationships with other entities. • Important features that can be shown on the ER diagrams: – – – Key constraints Participation constraint Weak entity Class hierarchy concept Aggregation concept CENG 351
Key Constraints • Many-to-many Mapping: Consider Works_In relationship : An employee can work in many departments; a dept can have many employees • One-to-many Mapping: Consider a Manages relationship between entities Employee and Departments. Each dept has at most one manager and each employee can manage more than one department (key constraint on Manages) • The Key constraint is represented by an arrow as in the related diagram. CENG 351
Key Constraints Departments have single manager, employee can manage more than one department! since name ssn dname lot Employees did Manages CENG 351 budget Departments
Types of relationship sets 1 -to Many-to-1 1 -to-1 Many-to-Many (one-to-one) (one-to-many) (Many-to-one) CENG 351
Participation Constraints: Total vs. Partial • If every department has a manager – then, this is a participation constraint: • the participation of Departments in Manages is said to be total (vs. partial). • For total participation implementation in relations, every did value in the corresponding Departments table must appear in a row of the Manages table, with a non-null ssn value, reflecting the situation that every department has a manager. • shown with bold line CENG 351
Participation constraints (cont. ) • Diagrammatic representation of key constraint and total participation since name ssn did lot Employees dname Manages CENG 351 budget Departments
Participation Constraints (cont. ) • Assume the case where each employee works in at least one department and that each department has at least one employee. • The example ER diagram shows Manages and Works_In in the same diagram, with correct properties. since name ssn did lot Employees dname Manages Works_In since CENG 351 budget Departments
Participation Constraints (cont. ) • Ensure total participation in the corresponding relationship tables is a problem! • We have to guarantee that every did value in Departments appears in a tuple of Works_In at least once or possibly more… CENG 351
The (min, max) notation (0, 1) (1, 1) Chapter 3 -16 (1, 1) (1, N)
Weak Entities • Some entities do not have to have a key. It is possible that some entities are dependent on other entities. That is their existence depends on the others… This dependent entity is known as weak entity. • 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 • Weak entity set must have total participation in this identifying relationship set. • A weak entity always has a partial key, it can only be uniquely defined if we take its key together with the key of the owner entity. CENG 351
Weak Entities (cont. ) • Assume an example of Employee and Dependents entities with a Policy relationship between tham. • Employee is owner entity, Dependent is weak entity, Policy is the identifying relationship set: name ssn lot Employees cost Policy CENG 351 pname age Dependents
ISA (`is a’) Hierarchies v v Some time it is more appropriate to represent entities in an entity set into subclasses, where attributes can be inherited down the hierarchy. As in object oriented programming tools, attributes are inherited. v If we declare A ISA B, every A entity is also considered to be a B entity. v As an example, consider a Employee entity as a super-class which has two subclasses as hourly and contract entities. In this case, the attributes of Employee should be inherited by the hourly and contract employee entities. CENG 351
name ISA (`is a’) Hierarchies ssn lot Employees Subclasses are defined first! hourly_wages hours_worked ISA Hourly_Emps contractid Contract_Emps • Overlap constraints: Can Zeynep be an Hourly_Emps as well as a Contract_Emps entity? (posibility this is not to be allowed to be overlapped. However if had a sub -class such as Senior employee entitiy, that can be have OVERLAP with the subclass Contract and or Hourly entities. ) • Covering constraints: Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity? (allowed using COVER). In some cases this may be required, in some other cases may not. • These two issues need to be represented by the programming tool used to implement such properties. CENG 351
ISA (`is a’) Hierarchies • Reasons for using ISA: – To add descriptive attributes specific to a subclass. – To identify the set of entities that participate in a particular relationship, e. g. only Senior Employees can be allowed to be Managers of Departments. CENG 351
Translating ISA Hierarchies to Relations • General approach: – 3 relations: Employees, Hourly_Emps and Contract_Emps. • Hourly_Emps: Every employee is recorded in Employees. For hourly emps, extra info recorded in Hourly_Emps (hourly_wages, hours_worked, ssn); must delete Hourly_Emps tuple if referenced Employees tuple is deleted). • Queries involving all employees are easy, those involving just Hourly_Emps require a join to get some attributes. • Overlap and covering constraints can only be expressed using assertions in case of SQL. CENG 351
Aggregation • Building a relationship between a collection of entities and relationships • Aggregation allows us to indicate a relationship set participate in another relationship set CENG 351
ssn Aggregation Suppose: • entity set Projects • each Projects is sponsored by at least one Departments • each Departments that sponsors a Projects might assign employees to monitor sponsorship Intuitively… ØMonitors should be a relationship set that associates a Sponsors relation (versus a Projects or Departments) with an Employees entity. name lot Employees since started_on pid pbudget Projects CENG 351 did Sponsors dname budget Departments
ssn Aggregation • Used when we have to model a relationship involving (entity sets and) a relationship set. • Aggregation allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships. name lot Employees Monitors since started_on pid pbudget Projects CENG 351 until did Sponsors dname budget Departments
Aggregation vs. Ternary relation ssn name lot Employees Each relationship in an aggregation may have a different descriptive attribute like since and until. Suppose: • No need to record until attribute of Monitors one can use ternary relationship Sponsors 2 ØThen… since started_on pid pbudget Projects did Sponsors 2 dname budget Departments But suppose we have constraint that: Each sponsorship (of a project by a department) be monitored by at most one employee? Then… one can’t do it with Sponsors 2 Need aggregated relationship Sponsors CENG 351
Aggregation vs. Ternary relation (Contd. ) ssn name lot Employees Monitors is a distinct relationship, with a descriptive attribute (until). until Ø since started_on pid pbudget Projects Also, can say that each sponsorship is monitored by at most one employee. Ø CENG 351 did Sponsors dname budget Departments
Conceptual Design Using the ER Model • Design choices: – 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? Aggregation? • Constraints in the ER Model: – A lot of data semantics can (and should) be captured. – But some constraints cannot be captured in ER diagrams. • Need for further refinement of the schema: – Relational schema obtained from ER diagram is a good first step. But ER design is subjective & can’t express certain constraints; so this relational schema may need refinement. CENG 351
Entity vs. Attribute • Should address be an attribute of Employees or an entity (connected to Employees by a relationship)? • Depending upon the purpose of address information and the semantics of the data: • If we have several addresses per employee, address must be an entity (since attributes cannot be set-valued). • 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). CENG 351
Entity vs. Attribute (Contd. ) • According to the conceptual ER model, Works_In 2 does not allow an employee to work in a department for two or more periods. from name ssn Employees v to lot did dname budget Departments Works_In 2 But we want several values of the descriptive attributes for each instance of this relationship. CENG 351
Entity vs. Attribute (Contd. ) • In this case, use Duration entity to allow more than one duration per relationship… ssn name lot Employees from did Works_In 3 Duration CENG 351 dname budget Departments to
Entity vs. Relationship • Following ER diagram OK if a manager gets a separate discretionary budget for each dept. since dbudget name ssn dname lot Employees did Manages 2 CENG 351 budget Departments
Entity vs. Relationship • What if a manager gets a discretionary budget which is the sum that covers all depts managed by that employee? Then the previous ER diagram will cause – Redundancy of dbudget, where the same value is stored for each dept managed by the same manager. – Misleading as it suggests dbudget tied to managed dept rather than employee. • Instead a separate entity Mgr_Appts can be used with apptnum as key. This way the redundancy is eliminated! CENG 351
Entity vs. Relationship name ssn dname lot Employees did Manages 3 budget Departments since apptnum Mgr_Appts dbudget CENG 351
Binary vs. Ternary Relationships • If each policy is owned by just 1 employee: – Key constraint on Policies would mean policy can only cover 1 dependent! • • Every policy must be owned by some employee Dependents is a weak entity set Thus, this can be represented as follows: This ternary relationship is a poor or inappropriate design! ssn name pname lot Employees Dependents Covers Policies policyid age cost CENG 351
Binary vs. Ternary Relationships(cont. ) • Are statement of the same application with more clarity: – A policy cannot be owned by more than one employee, every policy must be owned by some employee (total participation) and – Every policy must cover at least one dependent, dependent is a weak entity set. • Use a Beneficiary relationship which is an identifying relationship for the weak entity Dependents, ie. where each dependent will have one policy only… CENG 351
Binary vs. Ternary Relationships (cont. ) ssn name lot pname Employees age Dependents Purchaser Beneficiary Better design policyid Policies cost CENG 351 With two binary relationships
Binary vs. Ternary Relationships (Cont. ) • The key constraints allow us to combine Purchaser with Policies and Beneficiary with Dependents. • Participation constraints lead to NOT NULL constraints. • This example illustrated a case when 2 binary relationships were better than a ternary relationship. • It is also possible that ternary relationship is more suitable than binary, in some cases: CENG 351
Summary of Conceptual Design • Conceptual design follows requirements analysis, – Yields a high-level description of data to be stored • ER model popular for conceptual design – Constructs are expressive, close to the way people think about their applications. • Basic constructs: entities, relationships, and attributes (of entities and relationships). • Some additional constructs: weak entities, ISA hierarchies, and aggregation. • Note: There are many variations on ER model. CENG 351
Summary of ER (Contd. ) • Several kinds of integrity constraints can be expressed in the ER model: key constraints, participation constraints, and overlap/covering constraints for ISA hierarchies. Some foreign key constraints are also implicit in the definition of a relationship set. – Some of these constraints can be expressed in SQL. – Some constraints (notably, functional dependencies) cannot be expressed in the ER model. – Constraints play an important role in determining the best database design for an enterprise. CENG 351
Summary of ER (Contd. ) • 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. • Ensuring good database design: resulting relational schema should be analyzed and refined further. FD information and normalization techniques are especially useful. CENG 351
Relationship Set Ø The current “value” of an entity set is the set of entities that belong to it. üExample: the set of all books in our database. Ø The “value” of a relationship is a set of lists of currently related entities, one from each of the related entity sets. CENG 351
Some of the Automated Database Design Tools COMPANY TOOL FUNCTIONALITY Embarcadero Technologies ER Studio Database Modeling in ER and IDEF 1 X DB Artisan Database administration and space and security management Oracle Developer 2000 and Designer 2000 Database modeling, application development Popkin Software System Architect 2001 Data modeling, object modeling, process modeling, structured analysis/design Platinum Technology Platinum Enterprice Modeling Suite: Erwin, BPWin, Paradigm Plus Data, process, and business component modeling Persistence Inc. Pwertier Mapping from O-O to relational model Rational Rose Modeling in UML and application generation in C++ and JAVA Rogue Ware RW Metro Mapping from O-O to relational model Resolution Ltd. Xcase Conceptual modeling up to code maintenance Sybase Enterprise Application Suite Data modeling, business logic modeling Visio Enterprise Data modeling, design and reengineering Visual Basi and Visual C++
Examples • A University database contains information about professors (identified by social security number, or SSN) and courses (identified by courseid). Professors teach courses. • Each of the following situations concerns the Teaches relationship set. For each situation, draw an ER diagram that describes it (assuming that no further constraints hold). CENG 351
Examples (cont’) 1. Professors can teach the same course in several semesters, and each offering must be recorded. 2. Professors can teach the same course in several semesters, and only the most recent such offering needs to be recorded. (Assume this condition applies to all subsequent questions. ) 3. Every professor must teach some course 4. Every professor teaches exactly one course (no more, no less). 5. Every professor teaches exactly one course (no more no less), and every course must be taught by some professor. 6. Now assume that certain courses can be taught by a team of professors jointly, but it is possible that no one professor in a team can teach the course. Model this situation introducing additional entity sets and relationship sets if necessary. CENG 351
Example (cont’) 1) Professors can teach the same course in several semesters, and each offering must be recorded. CENG 351
Example (cont’) 2) Professors can teach the same course in several semesters, and only the most recent such offering needs to be recorded. (Assume this condition applies to all subsequent questions. ) CENG 351
Example (cont’) 3) Every professor must teach some course CENG 351
Example (cont’) 4) Every professor teaches exactly one course (no more, no less). CENG 351
Example (cont’) 5) Every professor teaches exactly one course (no more no less), and every course must be taught by some professor. CENG 351
Example (cont’) 6) Now assume that certain courses can be taught by a team of professors jointly, but no one professor in a team can teach the course alone. Model this situation introducing additional entity sets and relationship sets if necessary. CENG 351
CENG 351
Example • A company database needs to store information about employees (identified by ssn, with salary and phone as attributes); departments (identified by dno, with dname and budget as attributes); and children of employees (with name and age as attributes). Employees work in departments; each department must be managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company. CENG 351
CENG 351
ER DIAGRAM FOR A BANK DATABASE v© The Benjamin/Cummings Publishing Company, Inc. 1994, Elmasri/Navathe, Fundamentals of Database Systems, Second Edition
Скачать презентацию Database Design Conceptual Modeling with ER Model CENG
5d96a26aea29256338e447c61251ca08.ppt