SEMESTER ASSIGNMENT
DBMS BSCS Semester 04th
14 JUNE 2021
KUST
HANGU CAMPUS
This study source was downloaded by 100000887110707 from CourseHero.com on 07-02-2024 09:39:31 GMT -05:00
https://www.coursehero.com/file/155540180/Semester-Assignment-DBMSpdf/
Question# 01:
Design an ER schema diagram for this application, stating any assumptions
you make:
Suppose that a database is needed to keep track of student enrollments in classes and
students’ final grades. After analyzing the miniworld rules and the users’ needs, the
requirements for this database were determined to be as follows (for brevity, we show
the chosen entity type names and attribute names for the conceptual schema in
parentheses as we describe the requirements; relationship type names are only shown
in the ER schema diagram):
■ The university is organized into colleges (COLLEGE), and each college has a unique
name (CName), a main office (COffice) and phone (CPhone), and a particular faculty
member who is dean of the college. Each college administers a number of academic
departments (DEPT). Each department has a unique name (DName), a unique code
number (DCode), a main office (DOffice) and phone (DPhone), and a particular faculty
member who chairs the department. We keep track of the start date (CStartDate) when
that faculty member began chairing the department.
A department offers a number of courses (COURSE), each of which has a unique course
name (CoName), a unique code number (CCode), a course level (Level: this can be
coded as 1 for freshman level, 2 for sophomore, 3 for junior, 4 for senior, 5 for MS
level, and 6 for PhD level), a course credit hour (Credits), and a course description
(CDesc). The database also keeps track of instructors (INSTRUCTOR); and each
instructor has a unique identifier (Id), name (IName), office (IOffice), phone (IPhone),
and rank (Rank); in addition, each instructor works for one primary academic
department.
■ The database will keep student data (STUDENT) and stores each student’s name
(SName, composed of first name (FName), middle name (MName), last name
(LName)), student id (Sid, unique for every student), address (Addr), phone (Phone),
major code (Major), and date of birth (DoB). A student is assigned to one primary
This study source was downloaded by 100000887110707 from CourseHero.com on 07-02-2024 09:39:31 GMT -05:00
https://www.coursehero.com/file/155540180/Semester-Assignment-DBMSpdf/
academic department. It is required to keep track of the student’s grades in each section
the student has completed.
■ Courses are offered as sections (SECTION). Each section is related to a single course
and a single instructor and has a unique section identifier (SecId). A section also has a
section number (SecNo: this is coded as 1, 2, 3, . . . for multiple sections offered during
the same semester/year), semester (Sem), year (Year), classroom (CRoom: this is coded
as a combination of building code (Bldg) and room number (RoomNo) within the
building), and days/times (DaysTime: for example, ‘MWF 9am-9.50am’ or ‘TR
3.30pm-5.20pm’— restricted to only allowed days/time values).
(Note: The database will keep track of all the sections offered for the past several years,
in addition to the current offerings. The SecId is unique for all sections, not just the
sections for a particular semester.) The database keeps track of the students in each
section, and the grade is recorded when available (this is a many-to-many relationship
between students and sections). A section must have at least five students.
Question# 02
A database is being constructed to keep track of the teams and games of a sports
league. A team has a number of players, not all of whom participate in each game.
It is desired to keep track of the players participating in each game for each team,
the positions they played in that game, and the result of the game.
Design an ER schema diagram for this application, stating any assumptions
you make. Choose your favourite sport (e.g., soccer, baseball, football).
Question# 03
Consider the ER diagram shown in Figure 1 for part of a BANK database. Each
bank can have multiple branches, and each branch can have multiple accounts
and loans.
a. List the strong (non-weak) entity types in the ER diagram.
b. Is there a weak entity type? If so, give its name, partial key, and identifying
relationship.
This study source was downloaded by 100000887110707 from CourseHero.com on 07-02-2024 09:39:31 GMT -05:00
https://www.coursehero.com/file/155540180/Semester-Assignment-DBMSpdf/
c. What constraints do the partial key and the identifying relationship of the
weak entity type specifies in this diagram?
d. List the names of all relationship types, and specify the (min, max) constraint
on each participation of an entity type in a relationship type.
Justify your choices.
Figure 1: An ER diagram for a BANK database schema
This study source was downloaded by 100000887110707 from CourseHero.com on 07-02-2024 09:39:31 GMT -05:00
https://www.coursehero.com/file/155540180/Semester-Assignment-DBMSpdf/
Powered by TCPDF (www.tcpdf.org)
