Scribd
Upload
172 views2 pages

CP 121: Introduction To Database Systems Project Assignment (10 Marks) in Group of 10 Students Work On This Project, Duration 3 Weeks

This document describes an assignment to model a university database with entities like campuses, faculties, schools, programs, courses, students, lecturers, and committees. A group of 10 students have 3 weeks to: 1) Create an ER diagram modeling campuses, faculties, schools, programs, courses, students, lecturers, and committees. 2) Develop logical schemas from the ER diagram considering entity and referential integrity. 3) Create data dictionaries for 3 logical schemas including domain constraints. 4) Develop corresponding physical schemas in SQL Server and populate with sample data. 5) Demonstrate the ability to apply SQL DML commands to manipulate the data.

Uploaded by

Godfrey Joseph Patrick
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
172 views2 pages

CP 121: Introduction To Database Systems Project Assignment (10 Marks) in Group of 10 Students Work On This Project, Duration 3 Weeks

This document describes an assignment to model a university database with entities like campuses, faculties, schools, programs, courses, students, lecturers, and committees. A group of 10 students have 3 weeks to: 1) Create an ER diagram modeling campuses, faculties, schools, programs, courses, students, lecturers, and committees. 2) Develop logical schemas from the ER diagram considering entity and referential integrity. 3) Create data dictionaries for 3 logical schemas including domain constraints. 4) Develop corresponding physical schemas in SQL Server and populate with sample data. 5) Demonstrate the ability to apply SQL DML commands to manipulate the data.

Uploaded by

Godfrey Joseph Patrick
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

CP 121: Introduction to Database Systems

Project Assignment [10 Marks]


In group of 10 students work on this Project, Duration 3 Weeks
OS University is a large institution with several campuses. Each campus has a different name,
address, distance to the city center and the only bus running to the campus. Each campus has one
club. The name of the club, the building in which the club is located, the phone number of the
club and the multiple sports which club offers, should all be recorded.

The University consists of a number of faculties, such as the Art Faculty, the Science Faculty,
and so on. Each faculty has a name, dean and building. A faculty may be divided into a number
of schools, for example, the Science Faculty has a School of Physics and a School of Chemistry.
Each school belongs to one faculty only and is located on just one campus, but one campus
maybe the location of many schools.

Every school has name and building assigned to. Each school offers different programmes and
each programme can be offered by only one school. Each programme has a unique code, title,
level and duration. Each programme comprises several courses, different programmes have
different courses. Each course has a unique code and course title. Some courses may have one or
more prerequisite courses and one course can be the prerequisite course of some other courses.

Each of the students is enrolled in a single programme of study which involves a fixed core of
courses specific to that programme as well as a number of electives taken from other
programmes. Students work on courses and are awarded a grade in any course if he/she passes
the course. Otherwise the student has to re-take the failed course. The system needs to record the
year and term in which the course was taken and the grade awarded to the student. Every student
has a unique ID. The system also keeps the student name, birthday and the year he/she enrolled
in the course.

The school employs lecturers to teach the students. A lecturer is allowed to work for one school
only. Each lecturer is assigned an ID which is unique across the whole university. The system
keeps the lecturer’s name, title and the office room. A supervisor maybe in charge of several

1
lecturers, but a lecturer, however reports to only one supervisor. A lecturer can teach many
different courses. A course may also have been taught by many different lecturers.

The university is operated by committees. Each faculty has to have a number of committees with
the same titles across the university, such as the Faculty Executive, the Post Graduate Studies
Committee, the Health and Sanity Committee, and so on. The committees meet regularly, such as
weekly or monthly. The frequency is determined by the faculty involved. A committee’s
members are all lecturers. A lecturer may be a member of several committees.

Questions
a) Use the E-R Model to model the above scenario with E-R Diagram. State any
assumptions necessary to support your design
b) From the ERD in a) draw the corresponding logical schemas. Carefully observe both
Entity and Referential integrity constrains
c) From the logical schemas in b) choose at least three schemas and tabulate their
corresponding Data dictionaries. Carefully observe domain constrain on each attribute.
You may also suggest any general constrain that may apply on each attribute or relation
d) Create the corresponding Physical schema for the logical schema in b). With reference to
the types of values suggested in c) fill the physical schema with data. Use SQL Server as
your RDBMS
e) Be able to apply SQL- Data Manipulation Language (DML), the competence will be
evaluated during presentation session

You might also like