CHAPTER 2 FUNDAMENTAL OF ENTITY RELATIONSHIP ER Chapter

CHAPTER 2. FUNDAMENTAL OF ENTITY RELATIONSHIP (ER) Chapter 2 - ERD

2. 1 Entity Relationship (ER) Model 2. 1. 1 Introduction • ER model is the constructs and conventions used to create a model of the user data. • The results are documented in an entity relationship diagram (ERD) • ERD is a diagram that depicts entity relationship model entities, attributes and relations. It also displays connectivity and cardinality. Chapter 2 - ERD

e. g : A Car Purchasing Model RELATIONSHIP PRODUCE FACTORY PURCHASED BY CAR CUSTOMER ENTITY The diagram above shows a car purchasing model that involves the relationship between the factory, car and customer. Here, we can see clearly that factory produces car and a customer purchases the car Entity : FACTORY, CAR & CUSTOMER Relationship : Produce & Purchased by. Chapter 2 - ERD

e. g: ER Model of Book Information System PUBLISHER BOOK publish AUTHOR Written by • Books at E-Book bookstore are recorded. Information about publisher and the author will also be recorded to make sure that the process of searching for the book is easier Chapter 2 - ERD

Transformed to ERD Step 1 : Publisher_Code Type Publisher_Name Book_Code Price 1 PUBLISHER M publish BOOK Title city Publisher_Code Relationship One to Many (1: M) : One publisher can publish many books while one book can only be published by one publisher Chapter 2 - ERD

Step 2 : Book_Code Author_No Type Price Author_No M BOOK Title Publisher_Code N publish AUTHOR Book_Code Author_Name Relationship Many to Many (M: N) : One book can be written by many authors while one author can write many books Chapter 2 - ERD

Complete ERD Publisher_Code PUBLISHER Type 1 Price Book_Code Publisher_Name M publish BOOK M city Title Publisher_Code Written by Book_Code Author_No N AUTHOR Author_No Chapter 2 - ERD Author_Name

2. 1. 2 Elements of ER Model • There are 3 elements of ER Model : Element Entity Symbol Definition • A person, place, object, event or concept which you want to store information in database Attribute • A property or characteristic of an entity or relationship type. Relationship • Interaction of two entities represented by a verb. Chapter 2 - ERD

2. 1. 3 Notation of ER Model Entity Relationship Simple Attribute Multi valued Composite Attribute Derived Attribute Chapter 2 - ERD

2. 2 Entities And Attributes 2. 2. 1 Entity Definition • Entity is a person, place, object, event or concept which you want to store the information in a database Chapter 2 - ERD

Continue…. . Characteristics of entities : • Must have an attributes and unique key • Will become a table in a database Classification of entities : PERSON Example : Student, Employee PLACE OBJECT Example : Country, Branch Example : Product, Building EVENT Example : Student Registration, Payment CONCEPT Example : Course, Order Chapter 2 - ERD

Continue……. . What is Entity Type ? • Entity type is a collection of entities, which share the same characteristics What is Entity Instances ? • Entity instances are data for entity type Chapter 2 - ERD

Example Two entity instances for BOOK : P 01 Entity Type : BOOK Access 2000 Processing Attribute : Book_Code M-G 1 Book_title Database Publisher_Code 76. 00 Description Yes Type Price P 02 Book_cover Concepts of Database Management P-H 1 Database 100. 00 No Chapter 2 - ERD

2. 2. 2 Attribute • Attribute is a character of an entity or object. Example : Type Price Book_Code BOOK Title Attributes for the entity BOOK are Book_code, Type, Price and Title Chapter 2 - ERD

2. 2. 3 Types of Attributes Types of attributes Simple Multi valued Composite Definition Example • An attribute that holds a single value • e. g: The majority of people have only one name Name • An attribute that have more than one value • e. g: A staff may have 2 tel_no. Pnone-Num • An attribute that can be broken down into component parts Town State Postcode Address Derived • An attribute where the values can be calculated from related Chapter attribute 2 - ERD Date_of_birth Age

2. 3 Relationship and Cardinality Constraint 2. 3. 1 Relationship Definition : Relationship is an association between entities, which is defined by a diamond shaped symbol in an ERD Chapter 2 - ERD

Continue…. 2. 3. 2 Cardinality Constraint Definition : The number of entity type, which is involved in a relationship. Types of cardinality constraint : a). One to One Relationship (1: 1) Def : Maximum one X for each Y and one Y for each X Manager_ID 1 MANAGER Branch_ID 1 manage X BRANCH Y An employee (manager) can manage only one branch at one time and each branch only has one manager. Chapter 2 - ERD

b). One to Many (1: M) Def : Maximum one X for each Y and possibly many Y’s for each X. 1 FACULTY Faculty_Name Course_Code Faculty_Name M offer X COURSE Y At university, one faculty offers many courses for students but one course is offered by one faculty only. Chapter 2 - ERD

c). Many to Many (M: N) Def : Possibly many X’s for each Y and many Y’s for each X Author_ID ISDN M BOOK Author_ID N Written by ISDN A book may have more than one author and an author may write more than one Chapter 2 - ERD book. AUTHOR Composite entity Author_ID & ISDN : Composite Key

2. 4 Relational Key and Integrity Constraint 2. 4. 1 Types of Key Definition Candidate key An attribute or group of attributes that identifies a unique row in a relation. One of the candidate keys is chosen to be the primary key Primary Key Any of the candidates that are selected to be the key or an attribute that uniquely identifies each row in a relation. Foreign Key An attribute in a one table whose values must match the primary key in another table or whose value must be null. Attribute that act as a primary key in a related table. Composite Key A primary key that consists of more than one attribute, especially in M: N relationship. Chapter 2 - ERD

Continue…. 2. 4. 2 Integrity Constraints • 2 types of integrity constraint a). Entity Integrity Requirement : All entries are unique and no part of a primary key may be null (no data). Chapter 2 - ERD

Continue…. b). Referential Integrity Requirement : If foreign key exist in one table then the foreign key value must reference an existing primary key value in a table to which it is related and it can be null. Chapter 2 - ERD

Example : PUBLISHER Publisher_Code Name City F-B 1 Fajar Bakti Malaysia M-G 1 Mc. Graw Hill UK P-H 1 Prentice Hall UK T-H 1 Thompson US BOOK Book_Code Book_Title Publisher_Code Type Price P 01 Access 2000 Processing M-G 1 Database 76. 00 P 02 Teach Yourself MS Access M-G 1 Database 80. 00 P 03 Concepts of Database Mgmt System P-H 1 Database 100. 00 P 04 Pengajian Malaysia T-H 1 Pengetahuan am 40. 00 P 05 Graphic Design F-B 1 Graphic 88. 00 Publisher_Code is a Primary Key for PUBLISHER table, but it is also a Foreign Key for BOOK table. Please note that Foreign Key value in table BOOK must match the Primary Key value in PUBLISHER table. Chapter 2 - ERD

Entity Relationship Diagram (ERD) Definition : • ERD is a diagram that depicts entity relationship model entities, attributes and relations. It also displays connectivity and cardinality. • A tool used to represent graphically the association between entities in a system. View a system in term of entity and relationship. Chapter 2 - ERD

Steps to develop ERD IDENTIFY ENTITY IDENTIFY RELATIONSHIP SKETCH ERD STATE CARDINALITY CONSTRAINTS SPECIFY PRIMARY KEYS ADD ATTRIBUTES Chapter 2 - ERD

Exercise Hospital Jaya is based in KL. This hospital has a few wards with several patients. This wards has a few types, which depends on type of patients. Patient will be recorded into file that has ID Patient, Patient Name, Address, Phone Number, IC Number, Categories either VIP or regular patient and a group of blood patient Each patient is treated by a doctor at one time but one doctor can treats a number of patients. The information of doctors recorded are : Doctor ID, name, address, phone number, IC number and specialization. The nurses will be assigned to take care of the patients. Nurses in this hospital are graded based on their experience, qualification and duration of working in the hospital. Draw a complete ERD based on the situation Chapter 2 - ERD