WORKBOOK ACTIVITY
DFC 2033 DATABASE SYSTEM
Table of Contents TOPICS
PAGES
LAB ACTIVITY 1: Fundamentals of Database Management System LAB ACTIVITY 2: Relational Databases
3 11
LAB ACTIVITY 3: Relational Algebra
18
LAB ACTIVITY 4: Entity Relationship Model
25
LAB ACTIVITY 5: Structured Query Language (SQL)
32
LAB ACTIVITY 6: Database Transaction Management
54
Page 2 of 9
DFC 2033 DATABASE SYSTEM
Lab Activity 4: Entity Relationship Diagram Duration: 4 Hours
Learning Outcomes This activity encompasses activities 4A, 4B and 4C At the end of this activity session, you should be able to: 1. Identify the basic elements of ERD. 2. Design E-R Diagram based on a given scenario by using Chen’s Model and Crow’Foot 3. Convert a given ERD into relational tables.
Activity 4A Activity Outcome: Identify the basic elements of ERD and design the ERD based on a given scenario by using Chen’s Model. Nena Electrical Co. Wishes to create a database with the following entities and attributes: a. Customer, with attributes: CustomerID, Name, Email, Telephone b. Location, with attributes: LocationID, Address, Type c. Rate, with attributes: , RateID, RateClass, Rateperkwh After the interviews with the owner you have come up with the following business rules: ● Customer can have one or more locations ● Each location can have one and only one customer ● Each location can have one or more rates ● Each rate may be used at many locations, or not used at a location 1. Identify all the basic elements of ERD that are involved based on the above scenario: a) Entity and Attribute Entity
Customer Location Rate
Attribute
CustomerID, Name, Email, Telephone LocationID, Address, Type RateID, RateClass, Rateperkwh
b) Relationship, Connectivity and Cardinality
Customer
has
Location
Page 3 of 9
DFC 2033 DATABASE SYSTEM
c) Identifier keys Entity
Primary Key
2. Draw the E-R Diagram using Chen’s Model based on the Activity4A(1) that you have identified.
Page 4 of 9
DFC 2033 DATABASE SYSTEM
Activity 4B Activity Outcome: Identify the basic elements of ERD and design the ERD based on a given scenario by using Crow’s Foot. An automobile insurance company needs to keep track of information about vehicle. The company has to store information of customer, car and accident. Customer information is LicenseNo, name and address. Customers can own one or more cars, where the car information is PlateNo, model and year. A car not involved or involved in many accidents. If the car is involved in an accident, the information that will be stored is Report_number, location and date. 1. Identify all the basic elements of ERD that are involved based on the above scenario: a) Entity and Attribute Entity
Customer Car Accident
Attribute
LicenseNo, Name, Address PlateNo, Model, Year ReportNumber, Location, Date
b) Relationship, Connectivity and Cardinality
Customer
Car
own Car involve Accident
c) Identifier keys Entity
Customer Car Accident
Primary Key
LicenseNo PlateNo ReportNumber
Page 5 of 9
DFC 2033 DATABASE SYSTEM
2. Draw the E-R Diagram using Chen’s Model based on the Activity4B(1) that you have identified.
Page 6 of 9
DFC 2033 DATABASE SYSTEM
Activity 4C Activity Outcome: Convert a given ERD into relational tables. 1. Convert the E-R Diagram based on the Activity 4A(2) into the relational tables.
Customer CustomerID
Telephone
Name
Email
Location LocationID
Address
Type
CustomerID
Rate RateID
RateClass
Rateperkwh
Use RateID
LocationID
2. Convert the E-R Diagram based on the Activity 4B(2) into the relational tables.
LicenseNo
PlateNo
Model
Customer Name
Address
Car Year
LicenseNo _ _ _ _ _ _
Car_Accident PlateNo ReportNumber Accident ReportNumber Location
Date
Page 7 of 9
DFC 2033 DATABASE SYSTEM
Activity 4D Activity outcome : Using Chen Model Notation, Draw the ERD for the given scenario below. There are many soccer teams in Liga Super Malaysia ; each team has an ID,name,main stadium, and which city this team belongs.Each team has many players,and each player belongs to one team.Each player has a number,name,DOB,start year and shirt number that he uses.Teams play matches, for each match you need to keep track the date on which the game is played, match ID and the final result of the match.Each match has exactly three referees.For each referee have an referee ID,name,DOB and year of experience. (10 Marks)
Page 8 of 9
DFC 2033 DATABASE SYSTEM
Activity 4E Activity outcome : Using Crow’s Foot Model Notation, Draw the ERD for the given scenario below.
Seremban Hospital has several wards. This hospital has a few types of ward depends on types of patients. Patient information will be recorded into file that patient number, patient name, address, phone number, IC number, patient and 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, doctor name, address, phone no, IC no and expertise. 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.
(10 Marks)
Page 9 of 9
