DATABASE MANAGEMENT SYSTEMS

Assignment 1
QUESTION ONE
(a) Design an E-R schema for the following:
Consider a university delta’s database in which students register for courses
which are under specific departments. The data requirements are
summarized as follows:
- University delta has several departments. Each department has a unique
code and a name
- Each department offer courses. Each course has a name and an
identifying code
- A course has several units, each of which has a code and its given name
as per the course requirements
- Students can only register for a single course at a time. Upon registration
they are given unique registration numbers
(b) Draw the E-R Diagram
(c) Using the SQL create the database with the entities mentioned above, in
addition, create views.
(d) Using SQL insert data in all entities.
(e) Explain the role of the following DBMS components
i) Query processor
ii) Data manager

QUESTION TWO

(a) Design an E-R schema for the following:

A given train operates one and only one route while for a given route, we
can have more than one train operating. Passengers can book one or many
trains while a given train can be booked by more than one
passenger. Information that can keep regarding booking included the
passenger, the train they have booked, date of booking, and the seat booked.
Required:
 (b) Represent the above information using an Entity Relationship Diagram.
Use the crow feet notation. Include primary keys, foreign keys and
multiplicity constraints for the model.
(c) Create the database with the entities
(d) Explain how the following multi-user architectures work. Include a
diagram for each.
i) Three-tier architecture
ii) Transaction Processing Monitor (TPM)
(e) Discuss any Four limitations associated with the file-based approach. For
each, explain the nature of problems it posed with respect to data
operations.

QUESTION THREE

The schema below relates to a mini-world operation for an upcoming hotel.

Use it to answer the question that follows
Customer (CustID, Name, Email, DOB, gender, country)
Room (RoomNo, RoomName, price, RoomType)
Bookings (BookingID, RoomNo, CustID, BookingDate)
Additional information
CustID ,RoomNo, BookinID uniquely identifies all the entity occurrences of
customer , room and booking entities respectively
Required:

i) Write appropriate SQL statements to implement the above schema.

Specify the necessary constraints for each table.

ii) Write appropriate SQL statements to insert the following records for each table
as follows.
iii) Write an appropriate SQL statement to create a stored procedure
known as CustomerBooking( ) which accepts two parameters i.e.
customer number and Booking date then return bookings done by
the customer containing customer name, customer ID, Room No,
room price and booking ID fields.
iv) With respect to database systems, explain any TWO characteristics
associated with the database approach.
v) In the context of database systems, explain the categories of database users
and the role each plays.

QUESTION FOUR

(a) Define the following terms

(i) Database.
(ii) Attribute.
(iii) DBMS (database management systems).
(iv) Entity.
(b) Madaraka express which operates a fleet of trains on behalf of GoK
wishes to automate data storage operations and have come up with
the following list of data requirements
Data shall be captured about the customers who shall be booking the
 trains. The relevant data included customer number, name, email address,
mobile number, gender, and country of origin. The customer number
uniquely identifies each customer. The customer’s name shall be stored as
First name, Middle name and last name, and other names. A customer can
have one or more mobile numbers and email addresses.
Data shall also be captured about the trains the organization owns. Data to
be captured including TrainNo, Train Name, and capacity. The Train
number uniquely identifies each train. The train shall operate on a given
route. Information to be captured about a route includes route name, fare,
and a short description. The route name uniquely identifies each route. A
train can operate in one and only one route while in a given route, we can
have more than one route.
A customer can book one or many trains while a given train can be
booked zero or many times. Information captured about booking includes
bookingID, bookingDate, customer information, train information, and date
of travel.
REQUIRED:
(c) Using crow feet notation, represent the above information by drawing an
appropriate ER diagram. Primary keys, foreign keys, and multiplicity
constraints MUST be indicated.
(d) Write Clear SQL statements to create the database.
(e) Explain any three components of a database environment.

QUESTION FIVE

(a) UPS prides itself on having up-to-date information on the processing and
current location of each shipped item. To do this, UPS relies on a company-
wide information system. Shipped items are the heart of the UPS product
tracking information system. Shipped items can be characterized by item
number (unique), weight, dimensions, insurance amount, destination, and
final delivery date. Shipped items are received into the UPS system at a
single retail center. Retail centers are characterized by their type, uniqueID,
and address. Shipped items make their way to their destination via one or
more standard UPS transportation events (i.e., flights, truck deliveries).
These transportation events are characterized by a unique scheduleNumber,
a type (e.g, flight, truck), and a deliveryRoute.
Create an Entity Relationship diagram that captures this information about
the UPS system. Be certain to indicate identifiers and cardinality constraints.
 (b) Convert the ER diagram into a relational database schema. Be certain to
indicate primary keys and referential integrity constraints.
(c) Describe the process of data integration as applied to DBMS.
(d) Name the main steps in database design. What is the goal of each step?
In which step is the ER model mainly used?
(e) Database systems use different lifecycle models; describe them using a clear
diagram.

QUESTION SIX

(a) Computer Sciences Department frequent fliers have been complaining to

Dane County Airport officials about the poor organization at the airport.
As a result, the officials decided that all information related to the airport
should be organized using a DBMS, and you have been hired to design the
database. Your first task is to organize the information about all the airplanes
stationed and maintained at the airport. The relevant information is as
follows:
Every airplane has a registration number, and each airplane is of a specific
model. The airport accommodates a number of airplane models, and the
cache model is identified by a model number (e.g, DC-10) and has a
capacity and a weight. A number of technicians work at the airport. You
need to store the name, SSN, address, phone number, and salary of each
technician. Each technician is an expert on one or more plane model(s), and
his or her expertise may overlap with that of other technicians. This
information about technicians must also be recorded. Traffic controllers
must have an annual medical examination. For each traffic controller, you
must store the date of the most recent exam. All airport employees
(including technicians) belong to a union. You must store the union
membership number of each employee. You can assume that each employee
is uniquely identified by a social security number. The airport has a number
of tests that are used periodically to ensure that airplanes are still airworthy.
Each test has a Federal Aviation Administration (FAA) test number, a name,
and a maximum possible score. The FAA requires the airport to keep track
of each time a given airplane is tested by a given technician using a given
test. For each testing event, the information needed is the date, the number
of hours the technician spent doing the test, and the score the airplane
received on the test.
 1. Draw an ER diagram for the airport database. Be sure to indicate the
various attributes of each entity and relationship set; also specify the key
and participation constraints for each relationship set. Specify any
necessary overlap and cover constraints as well.

2. The FAA passes a regulation that tests on a plane must be conducted by a

technician who is an expert on that model. How would you express this
constraint in the ER diagram? If you cannot express it, explain briefly.
(b) With the help of a well-labeled diagram describe a client/server architecture.
(c) Give five advantages and disadvantages of client/server architecture.

QUESTION SEVEN

Design a database for a bank, including information about customers and

their accounts. Information about a customer includes their name, address,
phone, and Social Security number. Accounts have numbers, types (e.g.,
savings, checking), and balances. Also, record the customer(s) who own an
account.
(a) Draw the E /R diagram for this database. Be sure to include arrows where
appropriate, to indicate the multiplicity of a relationship.
(b) Convert ER diagram into a relational database schema. Be certain to
indicate primary keys and referential integrity constraints.
(c) What two concepts are ER modeling based on?
(d) Using SQL write proper statements to create the database.

QUESTION EIGHT

Suppose that you are designing a schema to record information about reality shows
on TV. Your database needs to record the following information:
For each reality show, its name, genre, basic_info, and participants name. Any
reality show has at least two or more participants.
For each producer, the company name, and company country. A show is produced
by exactly one producer. And one producer produces exactly one show.
For each television, its name, start year, and head office. Television may broadcast
multiple shows. Each show is broadcasted by exactly one television.
For each user, his/her username, password, and age. A user may rate multiple shows,
and a show may be rated by multiple users. Each rating has a score of 0 to 10.
(a) Draw an entity-relationship diagram for this database.
 (b) Convert ER diagram into a relational database schema. Be certain to
indicate primary keys and referential integrity constraints.
(c) Write a proper SQL to create the database and the tables through the
database schema.
(d) Define the trigger, view, and index.

NOTE:
1- For all E-R diagrams in this assignment use MySql workbrench to design
them.
2- For all database schemas use PPT or other tools to design them.
3- Prepare a PPT that includes all the steps of modeling, and creating a
database.
4- Submit an electronic copy that includes: file text.sql which contains the sql
queries, ER diagram model, and PPT presentation.
5- Deadline is :09/07/2022