Introduction to Database Design Methodology

1. Introduction to Database Design Methodology Chapter 3 Basic Concepts of Entity-Relationship Model

2. Learning Goals • To describe the basic stages in database design • To understand the basic concepts of entity-relationship model in conceptual database design • To identify the entities, attributes and relationships in simple scenarios

3. Table of Contents • Overview of Database Design • Entity-Relationship Model • Entity • Entity Set • Attribute • Relationship

4. Overview of Database Design

5. Scenario of an Online Bookstore Consider the following scenario • The owner of an online bookstore wants to create an information system for online sale of books. People will be allowed to register as customers of the online website. Customer information like customers’ correspondence addresses, e-mail addresses, and credit card numbers are to be maintained. In a single order, customers will be allowed to purchase any quantity of books. Items in a single order will be shipped to the customer.

6. Scenario of an Online Bookstore Problem: • How to design a database system that can satisfy the needs of the above online bookstore?

7. Scenario of an Online Bookstore • The above description may be considered as part of the user requirements of an online bookstore. • User requirements are usually collected by some fact-finding techniques, such as, examiningdocumentation, interviewing, observations, questionnaires, research, etc. We will not study such techniques in this part of the course. • Our main objective is to design a well-structured database based on the user requirements collected.

8. Database Database Design Logical database structure Design the Database User_1’s requirements How? All users’ requirements User_2’s requirements User_n’s requirements

9. Database Design • A systematic approach to database design consists of three main stages: • Conceptual database design • Logical database design • Physical database design

10. All users’ requirements Conceptual Database Design Logical Database Design Database Physical Database Design / Implementation Database Design

11. Database Design • Conceptual database design • To build the conceptual model of the database. This includes identification of the main data objects and relationships among the objects in the system. • We will study the conceptual design in this and the next chapter. • Logical database design • To translate the conceptual model into the logical structure of the database. This includes designing the table structures in relational database. • We will study the logical design in Chapter 5. • Physical database design • To design how the logical structure is to be physically implemented in the target Database Management System (DBMS).

12. Database Design • Referring to the three levels of data abstraction in database (Chapter 2) • Conceptual and logical designs correspond to the design of logical structure of the database in the conceptual level. • Physical design corresponds to the design of physical implementation of the database in the physical level.

13. Database Design View Level Conceptual Design Conceptual Level Logical Design Physical Level Physical Design

14. Database Design • We will only study the conceptual and logical database designs in this part of the course. • You will learn • Conceptual database design in the rest of this chapter and in Chapter 4 • Logical database design in Chapter 5

15. Entity-Relationship Model

16. Conceptual Database Design • The main objective of conceptual database design is to design the conceptual model of a database. • A conceptual model is a representation of the main data objects and relationships among the objects in a system (e.g. a school library system). • The most widely used conceptual model in database design is the entity-relationship (ER) model. • ER model is usually represented graphically in the form of an entity-relationship (ER) diagram, which will be introduced in Chapter 4.

17. Entity-Relationship Model • Entity-Relationship (ER) model views the real world as a collection of entities and relationships among entities. • Before we start to design the ER model of a database system, let us study some basic terminologies in ER model. • Remark: You may have learnt the concepts of ER model in the other topic of the Databases option. As these concepts are very important in conceptual database design, let us review these concepts before we study the design methodology.

18. Entity-Relationship Model • The basis terminologies used in ER model are: • Entity • Entity Set • Attribute • Relationship

19. Entity-Relationship Model • Entity • An entity is a real-world object that is distinguishable from other objects. • Entities may be physical (e.g. a student) or non-physical (e.g. an event in sport day, an exam). • Some examples of entities are: a student, a teacher, a book, an employee, an examination, etc. • In designing database, we should consider those entities that are of interest to us, i.e. those related to the user requirements.

20. Entity-Relationship Model I’m also an entity I’m an entity Exam I’m an entity I’m an entity I’m an entity I’m an entity I’m an entity

21. Entity-Relationship Model • Attribute • An attribute describes a particular characteristic of an entity. • Each entity has a set of attributes. • The attributes represent the data that we want to keep about each entity. • For example, • stud_id, name, dob, sex are possible attributes of a student; • ISBN, title, author, publisher are possible attributes of a book.

22. stud_id name dob sex Entity-Relationship Model Attributes of a student Entity: a student

23. Attributes of a book title author ISBN publisher Entity: a book Entity-Relationship Model

24. Entity-Relationship Model • Domain of Attribute • An attribute can only take certain values. The set of all possible values that can be taken by an attribute is known as the domain of an attribute. • Remark: domain of an attribute can be considered as the data type of an attribute. Some examples of data type are: character (string), integer, real number, date, etc.

25. Entity-Relationship Model • Domain of Attribute • For example, the entity STUDENT has the following attributes with the corresponding domains / data types.

26. Entity-Relationship Model Attribute Attribute value stud_id = “s0012” name = “Chan Tai Man” dob = #1997/1/1# sex = “M” Attribute Attribute value stud_id = “s0022” name = “Wong May May” dob = #1998/2/5# sex = “F” Same type of entity

27. Entity-Relationship Model AttributeAttribute value ISBN = “0-021-74387-6” title = “Database System” author = “Chan TM” publisher = “EA Education Co.” Attribute Attribute value ISBN = “0-07-111180-8” title = “Network System” author = “Peter Chan” publisher = “AB Publication Co.” Same type of entity

28. Entity-Relationship Model • Entity Set • An entity set is a collection of similar entities, i.e. entities sharing the same types of attributes. • For example, a set of all students in a school is an entity set STUDENT. Here STUDENT is a name given to the entity set of all students in the school. • Remark: In database design, sometimes an “entity” and an “entity set” may be used interchangeably. For example, “an entity STUDENT” may sometimes refer to “an entity set STUDENT”.

29. Entity-Relationship Model Entity set: STUDENT • Entity Set Entity set: BOOK Entity set: TEACHER

30. Entity-Relationship Model Entity Set: STUDENT stud_id name Student 1 Student 2 Student 3 Student 4 Student 5 Student 6 dob sex Student 7 Entities in the same entity set share the same types of attributes.

31. Entity-Relationship Model • Primary Key • A primary key is a minimal set of attributes whose values uniquely identify an entity in an entity set. • The primary key is usually represented by underlining the corresponding attribute(s). • Examples: • For the entity set STUDENT with attributes stud_id, name, dob, sex, the primary key is stud_id • For the entity set STUDENT with attributes class, class_no, name, dob, sex, the primary key is the combination of class and class_no

32. Entity-Relationship Model AttributeAttribute value stud_id = “s0012” name = “Chan Tai Man” dob = #1997/1/1# sex = “M” Primary key AttributeAttribute value stud_id = “s0022” name = “Wong May May” dob = #1998/2/5# sex = “F” Entity Set: STUDENT

33. Activity 1

34. Entity-Relationship Model • Try to do Activity 1 of Chapter 3

35. Entity-Relationship Model • Relationship • A relationship is an association between two or more entities. • For example, in the description: “a teacher teaches a student”, • the entities are teacher and student. • the relationship between the entities teacher and student is “teach(es)”: a teacherteaches a student relationship entity entity

36. Entity-Relationship Model Relationship: Teaches Student 1 Student 2 Teacher 1 Student 3 Student 4 Teacher 2 Student 5 Student 6 Teacher 1 Entity Set: TEACHER Student 7 Entity Set: STUDENT

37. Entity-Relationship Model • After defining the basic terms, let us summarize the objectives of conceptual database design as follows: • Identifying the entities/entity sets of interest in a system. • Identifying the attributes of each entity to be recorded. • Identifying the relationships among the entities. • Constructing the entity-relationship (ER) model of the database. • Remark: The ER model is usually constructed in the form of an ER diagram, which will be introduced in Chapter 4.

38. Guidelines on Identifying Entities • Guidelines on Identifying Entities: • Entities are the objects that are of interest to our system. • To identify the entities, look for the objects in the organization and in the user requirements. • As an entity/entity set represents many objects which share common properties, we should give a general name to an entity instead of the name of a particular object. For example, use the entity name “STUDENT” instead of “ChanTaiMan”.

39. Guidelines on Identifying Entities • Remarks: • For a given system, different database designers may not identify exactly the same set of entities. Therefore, they may get different database designs. Certainly, some designs would be better than the other. • It is important to identify the entity sets carefully because they are potentially the tables in relational database. (Refer to next slide)

40. Guidelines on Identifying Entities Entity set AttributeAttribute value stud_id = “s0012” name = “Chan Tai Man” dob = #1997/1/1# sex = “M” AttributeAttribute value stud_id = “s0022” name = “Wong May May” dob = #1998/2/5# sex = “F” Table in database

41. Guidelines on Identifying Attributes • Guidelines on Identifying Attributes: • To identify attributes, look for the characteristics of the entities that are of interest to us. • They represent the data that we want to keep about each entity. In fact, we store the values of the attributes in the database.

42. Guidelines on Identifying Relationships • Guidelines on Identifying Relationships: • To identify relationships, look for the associations / links between entities. • The name of a relationship is usually an active or passive verb.

43. Examples of Relationship Involving Two Entities

44. Example - an Online Bookstore Consider the following description of an online bookstore again. • The owner of an online bookstore wants to create an information system for online sale of books. People will be allowed to register as customers of the online website. Customer information like customers’ correspondence addresses, e-mail addresses, and credit card numbers are to be maintained. In a single order, customers will be allowed to purchase any quantity of books. Items in a single order will be shipped to the customer.

45. Example - an Online Bookstore • The above description may be the way that the user (owner of the bookstore) understands the system. • Sometimes, the description is filled with ambiguities and irrelevant information, together with fairly precise descriptions of the real situation. • We need to analyze the description and make an initial list of entity sets, attributes and relationships for the system

46. Example - an Online Bookstore • The owner of an online bookstore wants to create an information system for online sale of books. People will be allowed to register as customers of the online website. Customer information like customers’ correspondence addresses, e-mail addresses, and credit card numbers are to be maintained. In a single order, customers will be allowed to purchase any quantity of books. Items in a single order will be shipped to the customer. Here, we identify the entities, attributes and relationships by using the following colours: entity relationship attribute

47. Example - an Online Bookstore • Entity Sets: BOOK, CUSTOMER • Relationship: purchase • Description: Customer purchases books

48. Example - an Online Bookstore • Attributes of BOOK: • Book_Rec_No • ISBN • Title • Author • Publisher • Attributes of CUSTOMER: • Customer_ID • Correspondence_address • e-mail • Credit_card_no

49. Example - an Online Bookstore • Remark: • The attributes of BOOK are not mentioned in the given description. We may sometimes need to find out further information about the system when necessary. • To uniquely identify each entity [instance] that are of the same entity set, a primary key (the underlined attribute) is added for each entity. • We may also identify ORDER as an entity / entity set of the system. We will consider such case in Chapter 4.

50. Activity 2