TERM 3, 2017
Assignment
AIM:
Design of Database using Top Down method involves developing a database for the given case study by drawing the EER diagram and translating the EER diagram into a set of relations. This assignment discusses the key issues to be considered in drawing the EER diagram with different types of relationships and cardinality ratios.
Translation of EER diagram into a set of relations.
Provide the data dictionary for the relational schema designed using Top-Down-method.
Design of Database using Bottom Up method: To identify the Functional Dependencies and hence the BCNF relations for the given scenario. Identification of foreign keys in the BCNF relations.
De-normalization of the Relations.
CASE STUDY A
BICYCLE RENTAL FACILITY (BRF)
Singapore’s Social Welfare Service (SWS) section has planned to introduce Bicycle Rental Facility (BRF) to its citizen. BRF not only provides a great way to provide citizens with safe, convenient and environmentally friendly transportation on their schedule, but also helps in improving their health as a physical exercise.
Bicycles are housed in service terminals placed at a number of locations across the country.
Each service terminal is given a unique identification number, terminal name, street address, telephone number and maximum storage capacity. The government keeps track of the bicycles currently available at each service terminal.
Bicycles are identified by the unique ID of each bicycle, along with its make, model, color, size and type (kid, adult and senior citizen) of bicycle.
While the government provides the bicycles and maintains the terminals, private-sector companies are also invited to sponsor individual bicycles and terminals and, in return for a fee, their logo will be displayed prominently on the sponsored asset. So owner of a Bicycle may be government or a sponsor.
The name of each sponsoring company along with their address, contact name, email, and telephone number are registered.
A user interested in using the bicycle facilities must register as member. Information stored about each member includes full name, email, contact phone numbers, residential address, membership status and member fee. Members may be children between 12 to 16 ages, adults and senior citizens.
Membership status and member fee are fixed accordingly.
In addition to memberships, visitors or casual users can use BRF by purchasing a $5 one-day pass (valid for 24 hrs) using a credit card. The visitors detail, full name, email, contact phone numbers, residential address are recorded while collecting a bicycle. The card has a number, date and time of purchase.
Each rental transaction is fully automated and records details of the user, bicycle, pick-up time and location, drop-off time and location, and total rental length. The billing is done based on the total rental length. The rental rate depends upon the type of Bicycle. All the transactions in a month are billed and sent to the members. Each bill has a bill number, date and total amount to be paid. Each bill need a payment. Payment detail include the payment number, payment date and mode of payment (cash or credit card).
A user may not borrow more than one bicycle at any given point in time. Once checked back in, the bicycle is immediately available to another customer for rental.
BRF also provides bicycle parking racks in different locations. Each parking rack has a number, name, location and capacity. Members can park the rented bicycle at any nearby location of their places.
QUESTION 1
Design of Relational Database using Top-Down Method.
In the design of a database, the first step is drawing the conceptual model (EERD).
Draw the EER diagram for the Case Study A – BRF.
EERD must include all the entities available in the case study.
All entities must include primary keys and attributes. (A maximum of 6 attributes may be assumed for each entity if necessary).
EERD must include the followings:
(a) Identify all the real world entities with Primary keys and attributes. [7 marks]
(b) Identify all super / sub-type entities available in the given case study. [6 marks]
(c) Identify a weak entity present in the case study. [1 mark]
(d) Identify any one relationship with a cardinality ratio 1:1. [1 mark]
(e) Identify any one relationship with a cardinality ratio 1:N. [1 mark]
(f) Identify any one relationship with a cardinality ratio N:M. [1 mark]
(g) Identify an entity with a multi-valued attribute. [1 mark]
(h) Identify any one ternary relationship available in the given case study. [2 marks]
(i) Draw the complete EER diagram. [5 marks]
[TOTAL FOR QUESTION 1: 25 MARKS]
QUESTION 2
Translation of the EER diagram from Q1 into a set of relations.
Use the eight steps algorithm of converting EERD into a set of relations, given in the text book by Elmasri and Navathe, to translate your EER model to a relational model.
(a) Translation of Real world entities[7 marks]
(b) Translation of Weak entity [1 mark]
(c) Translation of 1:1 relationships[1 mark]
(d) Translation of 1:M relationships [4 marks]
(e) Translation of N:M relationships [1 mark]
(f) Translation of Multi-valued attributes [1 mark]
(g) Translation of Ternary relationship [4 marks]
(h) Translation of Super type / Sub-type entities [6 marks]
[TOTAL FOR QUESTION 2: 25 MARKS]
QUESTION 3
Provide the data dictionary documenting the attributes, data type and length of each data type, primary key and foreign key(s) for ANY FOUR relations of the relational schema obtained in Q2. Answer may be given in the following table format:
[4 relations x 2 marks each = 8 marks]
Relation
Attribute
Data Type
Size
Primary Key
Foreign Key
Referred Relation
[TOTAL FOR QUESTION 3: 8 MARKS]
CASE STUDY B
SAFE LIFE – HEALTH CLUB
SAFE LIFE, a health club, plans to design and develop a database to store the data and manage its information.
The health club has members and each member has an account with the health club. Each member account has an account number, the date the account was opened and a discount percentage which is applied to all fees charged to the membership account. The club maintains information about each member, such as the member id, first name, last name, date of birth, monthly fee, address and phone number.
The health club offers several classes, such as yoga, step aerobics, and spin classes. The database needs to maintain information about each type of class, such as the class id, class name, class price and class description. Each class is offered several times a week. The database needs to maintain the time and day schedule for each class. The schedule should also indicate the instructor for each offering of the class.
The health club needs to maintain information about instructors of classes. The information includes instructor identifier, name, room where the instructor is placed, telephone number, salary, classes that an instructor is scheduled to teach.
The club also contracts personal trainers for the members of the club to teach special body building techniques courses. Each member is attached to a personal trainer. Personal trainers charge a fee to members who contract their services for a specified duration (one month, three months, or six months).
The course information like course id, course name, duration, start date and fee are recorded in the database. The health club maintains information about personal-trainers’ id, name, contact- number and course taught.
The club provides special accessories for the courses on payment. Each course requires many accessories. Accessories are identified by its identifier number, name, description and price.
QUESTION 4
(a) Identify a single un-normalized relation for the above scenario CASE STUDY SAFE LIFE.
[3 marks]
(b) Identify a primary key of the un-normalized relation identified in Q4 (a). [2 marks]
(c) Identify all functional dependencies available in the un-normalized relation identified in Q4 (a).
[18 marks]
(d) Using the functional dependencies identified in Q4 (c), produce BCNF relations (Do not give 1NF, 2NF and 3NF relations).
[9 marks]
[TOTAL FOR QUESTION 4: 32 MARKS]
QUESTION 5
Application query on the BCNF relational schema identified in question 4 (d) requires frequent joining of several relations. Schema de-normalization can sometime provide better query performance.
Identify ANY TWO application queries that may require de-normalized relations on the relational schema from Question 4 (d). Also give the corresponding relational algebra expressions and de-normalized relations.
[10 marks]
[TOTAL FOR QUESTION 5: 10 MARKS]
[TOTAL MARKS: 100 MARKS]