Conceptual Design

1. Conceptual Design

2. Database Development Requirements Analysis Requirements Specification Natural Language Conceptual Design Conceptual Model Informal Logical Design Relational Model Formal DBMS-independent DBMS-specific Database Building Database Physical Operation, Mainte- nace, & Tuning Evolving Database

3. Database Development (Cont.) • Requirements Analysis • Collect and Analyze the requirements of the users. • Conceptual Design • Design a conceptual model (schema), e.g., ER model. • Logical Design • Translate the ER model into a relational model (schema). • Normalization. • Database Building (Physical Design) • Build the database and write application programs. • Operation, Maintenance, & Tuning • Use, maintain, and “tune” the database.

4. Conceptual Design • Design a conceptual model (or semantic model). • Semantic: “meaning” • The Entity-Relationship (ER) model, introduced by Peter Chen in 1976, has been the most popular conceptual model used in database design. • Basic idea: A database can be modeled as: • a collection of entities (things), and • relationships among entities. • The result is an ER Diagram (or Schema), which is easy to explain to the users and therefore can be used as a communication tool between the designer and the users.

5. What do We Model? • Mini-world , also known as the Universe of Discourse (UOD): Some part of the real world about which information will be stored in the database. • Mini-world is the scope of our modeling.

6. The Suppliers-Parts Database S# STATUS SUPPLIER SNAME CITY M SUPPLY QTY P# M WEIGHT PNAME PART CITY COLOR

7. Basic Constructs of ER • Entity • The mini-world is made up of entities. • An entity is a “thing” that is distinguishable from others. • e.g., a supplier S1, a part P1 • Relationship • Any entity can be related to other entities via relationships. • e.g., a supplier may supply some parts. • Property (Also known as Attribute) • Entities and relationships have properties (attributes). • e.g., a supplier has a S#, a name, a status, and a city.

8. Entity and Entity Type • Entity: • A “thing” that is distinguishable from others. • e.g., a supplier S1, a part P1. • Entity Type: • Entities can usefully be classified into entity types. • e.g. all suppliers are instances of the generic SUPPLIER entity type. • Entities of a given type have certain properties in common. • We model entity types, not individual instances. • However, people often use the term “entity” to refer to both entity type and instance.

9. Relationship and Relationship Type • Relationship: • Any entity can be related to other entities via relationships. • e.g., a supplier may supply some parts. • Relationship Type: • Relationships can usefully be classified into relationship types. • e.g. “S1 supply P1” and “S1 supply P2” are instances of the generic SUPPLY relationship type. • Relationships of a given type have certain properties in common. • We model relationship types, not individual instances. • However, people often use the term “relationship” to refer to both relationship type and instance.

10. Property (Attribute) • Entities and relationships have properties. • e.g., a supplier has a S# ‘S1’, a name ‘Smith’, a status 20, and a city ‘London’. • All entities or relationships of a given type have certain kinds of properties in common. • e.g., all suppliers have a S#, a name, a status, and a city. • A special property, or combination of properties, that serves to identify every entity of a given type is called an identifier (also called primary key). • e.g., S# is an identifier of SUPPLIER.

11. Property and Property Value • S# is a property of entity type SUPPLIER. • 'S1', 'S2', etc. are possible values of S# of some suppliers. • We model properties, not property values in the ERD.

12. Basic Notation • Entity • Relationship • Property • Identifiers are underlined. PART SUPPLY P# PNAME

13. Entity Type: Strong vs. Weak • Not every entity type has a primary key. Such an entity type is referred to as a weak entity type. • A weak entity is existence-dependent on some other entity, called its identifying entity. A weak entity can’t exist if its identifying entity does not also exist. • The relationship between a weak entity and its identifying entity is called identifying relationship. • e.g., an employee’s dependents might be weak entities. • They cannot exist (in the database) if the relevant employee does not exist. • If an employee is deleted, her dependents are deleted too.

14. Weak Entity Example • In an ERD, a weak entity type and its identifying relationship are double-lined. EMP# SALARY EMPLOYEE ENAME 1 EMP_DEP M DNAME GENDAR DEPENDENT

15. Identifying a Week Entity Type • A Weak entity type doesn’t have a primary key. • If X is a weak entity type and Y is the entity type on which X is dependent, • We form a primary key for X by combining the primary key of Y with one or more attributes, called discriminator or partial key, from X. • In an ERD, a partial key is usually dash-underlined. • e.g., primary key for DEPENDENT: {EMP#, DNAME}.

16. Relationship Type: Degree • Participant: The entities involved in a relationship are called participants in that relationship. • Degree: The number of participants in a relationship. • Binary, Ternary, and higher-degree. • A relationship whose participants are the same entity type is called a recursive relationship. • Binary relationships are the most often seen. Ternary or higher-degree relationships are rare. • Role: • Each participant plays some role in the relationship. • Role labels are optional. Sometimes it’s useful to draw the role labels for a recursive relationship.

17. Recursive Relationship • With role labels. Container M P# CONTAIN PART PNAME M Component COLOR 1 WIFE MARRY PERSON 1 HUSBAND

18. Ternary Relationship • Example: The suppliers-parts-projects database. • Can we answer the question: • Does supplier S1 supply part P1 for project J1? PART P# M S# J# SPJ M M SUPPLIER PROJECT QTY

19. Ternary vs Binary • A ternary relationship is NOT equivalent to three binary relationships. • Can we answer the question: • Does supplier S1 supply part P1 for project J1? PART P# M M SP PJ S# J# M M SJ M M SUPPLIER PROJECT

20. Ternary vs Binary (Cont.) • Example: A patient is treated by doctors via treatments. • Case 1: Each treatment is done by exactly one doctor on exactly one patient DOCTOR D# 1 Conduct P# T# M Treat 1 M PATIENT TREATMENT

21. Ternary vs Binary (Cont.) • Case 2: Each treatment may be done by one or more doctors on exactly one patient DOCTOR D# M Conduct P# T# M Treat 1 M PATIENT TREATMENT

22. Ternary vs Binary (Cont.) • Case 3: Each treatment (session) is done by exactly one doctor on one or more patients. DOCTOR D# 1 Conduct P# T# M Treat M M PATIENT TREATMENT

23. Ternary vs Binary (Cont.) • Case 4: Each treatment may be done by one or more doctors on one or more patients. Every doctor participating in a treatment is responsible for all the patients who are accepting the treatment. DOCTOR D# M Conduct P# T# M Treat M M PATIENT TREATMENT

24. Ternary vs Binary (Cont.) • Case 5: Each treatment (session) may be done by one or more doctors on one or more patients. Each doctor participating in a treatment is responsible for some (not all) of the patients who are accepting the treatment. DOCTOR D# M P# T# Treat M M PATIENT TREATMENT

25. Ternary is Rare • Ternary or higher degree relationship types are rare. • Whenever we want to introduce a ternary or higher degree relationship type, we consider whether several binary relationship types will do.

26. Total/Partial Participation Example • E.g., if every employee must belong to a department (total), while it is possible for a given department to have no employee at all (partial). DEPARTMENT DEPT# 1 DNAME WORK_IN M EMP# EMPLOYEE SALARY ENAME

27. Relationship Type: Cardinality • 1-to-1, 1-to-many, many-to-many • If R is a relationship type between entity types A and B, how many instances of B or A can be related to each instance of A or B. 1 WIFE MARRY PERSON 1 HUSBAND

28. Cardinality Example DEPARTMENT SUPPLIER 1 M 1 MANAGE WORK_IN SUPPLY M M EMPLOYEE PART 1

29. Property (Attribute) • Non-key vs. key • Simple vs. composite S# SUPPLIER SNAME EMP# FIRST EMPLOYEE MI ENAME LAST

30. Property (Cont.) • Single valued vs. multi-valued • Assume an employee may have multiple phone numbers • Base vs. derived EMP# EMPLOYEE PHONE SUPPLY M M SUPPLIER PART TOTQTY TOTQTY QTY

31. Entity Supertypes and Subtypes • People have extended Chen’s ER with various additional constructs, such as entity supertypes and subtypes. • E.g., if some employees are programmers, then we say entity type PROGRAMMER is a subtype of entity type EMPLOYEE, and EMPLOYEE is a supertype of PROGRAMMER. • Supertype/subtype represents an “ISA” relationship. • E.g., a programmer ISA employee.

32. Entity Type Hierarchy • An entity type A can be the subtype of entity type B and the supertype of entity type C, constituting a hierarchy. • E.g., EMPLOYEE has a subtype PROGRAMMER, which in turn has subtypes APP_PROG and SYS_PROG. EMP# EMPLOYEE ENAME PROGRAMMER LANGUAGE WEB_BASED APP_PROG SYS_PROG OS

33. Inheritance • All properties and relationships of the supertype apply automatically to, or are inherited by, the subtype. • But the converse is not true. The subtype may have some special properties or relationships that do not apply to the supertype. • E.g., all properties and relationships of EMPLOYEE apply to PROGRAMMER. PROGRAMMER has a special property LANGUAGE, which does not apply to EMPLOYEE. • We specialize an entity type into subtypes to capture special properties or relationships.

34. *Other ERD Notations • Various different notations have been proposed. • E.g., Crows-Feet notation • Diamond icons are replaced with lines. •  for “Zero” •  for “One” • for “or more” zero or one one or more zero or more exactly one Entity 1 Entity 2 Entity 3

35. Discussion: Entities vs. Relationships • The ER model distinguishes the two concepts. • However, the very same thing can quite legitimately be regarded as an entity by some users and a relationship by others. • A relationship is also an entity. • Example: • From one perspective, a marriage is clearly a relationship between two people. • “Who was Elizabeth Taylor married to in 1975?” • From another perspective, a marriage is equally clearly an entity in its own right. • “How many marriages have been performed in this church?”

36. *Associative Entity • Some people represent a relationship as an associative entity (also called bridge). DATE 1 WIFE SSN# MARRIAGE PERSON NAME 1 HUSBAND LOCATION

37. Summary • Entity • A distinguishable thing. • Relationship • An entity that serves to interconnect two or more other entities. • A relationship is also an entity, an associative entity. • Property • A piece of information that describes an entity. • Subtype • Entity type Y is a subtype of entity type X if and only if every Y is necessarily an X.

38. Design Notes • Entities are usually nouns, relationships are usually verbs. • No duplicated entity or relationship names. • Each statement in the requirement specification should be located somewhere in the ER diagram. • Each ER diagram construct should be located somewhere in the requirement specification. • “No more, no less.” • Conceptual design often reveals inconsistencies and ambiguities in the requirement specification, which must be first resolved.

39. An ERD Example DEPT# S# DNAME DEPARTMENT SNAME CITY 1 1 J# SUPPLIER STATUS JNAME MANAGE WORK_IN M M CITY CITY TOTQTY TOTQTY EMP# PROJ_WORK FIRST 1 M M M M MI SPJ SP NAME EMPLOYEE PROJECT 1 LAST 1 PROJ_MANAGE SALARY M QTY QTY PHONE P# PNAME M LANGUAGE M PROGRAMMER COLOR EMP_DEP PART WEIGHT M M CITY M Container Component TOTQTY APP_PROG SYS_PROG DEPENDENT CONTAIN QTY WEB_BASED OS DNAME GENDER

40. An ERD Example (Without Properties) DEPARTMENT 1 1 SUPPLIER MANAGE WORK_IN M M PROJ_WORK 1 M M M M SPJ SP EMPLOYEE PROJECT 1 1 PROJ_MANAGE M M M PROGRAMMER EMP_DEP PART M M M Container Component APP_PROG SYS_PROG DEPENDENT CONTAIN

41. The Suppliers-Parts-Projects DB S# SNAME J# SUPPLIER STATUS JNAME M M CITY CITY TOTQTY TOTQTY M SPJ SP PROJECT QTY QTY P# PNAME M M COLOR PART WEIGHT CITY TOTQTY

42. The Suppliers-Parts DB S# SNAME SUPPLIER STATUS M CITY TOTQTY SP QTY P# PNAME M COLOR PART WEIGHT CITY TOTQTY

43. Additional Constraints • Some integrity constraints can't be shown on the ERD. • The term integrity refers to the accuracy and correctness of data in the database. • These should be specified in a conceptual design specification. • The output of the conceptual design consists of both the ERD and the specification.

44. Constraints of the Suppliers-Parts DB • Weight is a real number in [0, 10000]. • QTY is a real number in [0, 60000]. • QTY must be entered for SP. • Color must be one of ('Red', 'Green', 'Blue', 'Yellow', 'White', 'Black', 'Other'), default is 'Red'. • Sname does not have duplicates. • Suppliers in London must have status 20. • No suppliers with status less than 20 can supply any part in a quantity greater than 500.