COMSATS UNIVERSITY, ISLAMABAD

Department of Computer Science

Course: Database Systems (CSC270) Class: -BCS-4

Mapped to CLO2 [Apply data modeling and normalization techniques to design database for small to medium
size enterprise]

For each of the following problems, first create an entity relationship (ER) diagram and then convert it into a relational
database schema. Assume attributes as per your real-world knowledge for the given scenarios, if not mentioned.

Problem-1: Cooking club Database [1]

A cooking club organizes several dinners for its members. The purpose of the club is to allow several members to get
together and prepare a dinner for the other members. The club president maintains a database that plans each meal and
tracks which members attends each dinner and keeps track of which members creates each dinner. Each dinner serves
many members, and any member is allowed to attend. Each dinner has an invitation. This invitation is mailed to each
member. The invitation includes the date of the dinner and location. Each dinner is based on a single entrée and a single
dessert. This entrée and dessert can be used again for other dinners.

Problem-2: Malam Jabba Store Database [2]

In Malam Jaba, there is a store that rents pair of skis and also sells snowboard to the tourists. The store is interested in
automating its sale and rental process. The store owner has gathered the following requirements and wants you to
design the database:
 The store contains two types of equipment: Pair of skis and snowboard. They are identified uniquely by their
equipment ID. For both the equipment, their model needs to be stored. In addition, for pair of skis, their rental
price, while for snowboard their sale price needs to be stored.
 Tourists that visit the store are identified by their passport numbers. In addition, their name, nationality and
multiple contact numbers also need to be stored.
 There can be two types of tourists: skier or snowboarder. For skiers we want to store their years of experience
too. Skiers rent pair of skis while snowboarders purchase snowboard.
 A Skier may rent one or more pair of skis and a pair of skis can be rented by many skiers throughout the
course of its life. For each rental, the store also wants to record the rental date and return date.
 A Snowboarder may purchase one or more snowboards. For each purchase, the store wants to record purchase
date too.

Problem-3: Football Match Database [3]

The database for Football Match needs to be built. The following set of requirements have been identified:
 Football Clubs play Games. Each football club have specific id, name, description, color, and other details.
 Football Clubs have specific Uniforms. Each Uniform has unique identification number, name, and
description.
 Football Clubs have specific Jargon, such a ‘Fumble’ in the NFL. Jargon have id, phases and meaning.
 Football Clubs have a range of Reference Material, such as Footballer Contracts. Reference identification,
reference material title and description.
 Games always involve two Football Clubs. Games have different details regarding game like date, results, id,
and description.
 Games are played at different Locations. Each location has id, name, and address.
 Games always produce Deliverables – such as Goals and Results. Deliverables has name, descriptions, and id.
  Football Clubs have Cub Personnel, including Players. Club personals have name, title, gender, address, email
address, contact details etc.
 Players are involved in Games as Game Players.
 Players in Games can play at specific Positions. Each position has certain details.
 Games can involve Value Systems, such as ‘Play by the Rules and Win’. Value system has id, name and
description.

Problem-4: Chughtai Group of Pharmacies Database [4]

You are to design a normalized relational database for Chughtai group of pharmacies. Here are the requirements
provided to you:
 Patients are identified by their CNICs. For each patient, the name, address, and age must be recorded.
 Doctors are identified by their CNICs. For each doctor, the name, specialty, and years of experience must be
recorded.
 Every patient has a primary doctor. Every doctor has at least one patient.
 Each pharmaceutical company is uniquely identified by name and has a phone number.
 For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical
company, and the trade name identifies a drug uniquely from among the products of that company only and
not across all the companies.
 Each pharmacy has a unique name, address, city, and phone number.
 Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and
the price could vary from one pharmacy to another.
 Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a
patient could obtain prescriptions from several doctors. Each prescription is assigned an ID, has a date and, a
quantity associated with it.