Professor Smith would like to assign m projects to n students in a database class. Each project can be described by its name, description, deadline and status (completed or in progress); each student has a student id, a name, and an email. Students can work in groups of several persons on one of the m projects. Different groups will take different projects (assume more projects than students, so
some projects will have no students assigned) and each student participates in exactly one group. After the project for a group is finished, a grade for the project is determined and given to all students in the group. Assume each group is identified by a unique group name. In the following, you are asked to help Dr. Smith design a database system to facilitate the assignment and grading of the projects,
(a) Draw an E-R diagram for the system, in particular, use arrows or thick lines to represent
constraints appropriately. Write down your assumptions if you need to make any.
(b) Translate the above E-R diagram to a relational model, in particular, specify your primary
key and foreign key constraints clearly. That is, give SQL CREATE statements that define the tables you need.
(c) Write an SQL statement to create a view that gives the project name and group name of
completed projects.
(a) The E-R diagram is shown below.
(b) The following CREATE SQL statements create the tables and constraints that correspond
to the above E-R diagram.
CREATE TABLE Student (
sid INTEGER,
sname CHAR(30),
email CHAR(50),
gname CHAR(30),
PRIMARY KEY sid,
FOREIGN KEY gname REFERENCES Group)
CREATE TABLE Project (
pname CHAR(100),
description CHAR(300),
deadline DATE,
status CHAR(15),
PRIMARY KEY pname,
CHECK(status IN ('Completed', 'Inprogress')))
CREATE TABLE Group (
gname CHAR(30),
grade INTEGER,
pname CHAR(100),
PRIMARY KEY gname,
FOREIGN KEY pname REFERENCES Project)
Since the relations between the entities are not of a many-to-many or many-to-one relationship,
we do not create any separate tables for them. The pname attribute is added to
the Group table and the gname attribute to the Student table.
(c) The view of completed projects:
CREATE VIEW CompletedProject AS
SELECT P.pname, G.gname
FROM Group G, Project P
WHERE G.pname = P.pname
AND P.status = 'Completed'
You might also like to view...
Explain when it might be preferable to use a map instead of a set
What will be an ideal response?
How can knowing the vendor and version of the database be useful for an intruder?
What will be an ideal response?
The amplitude of a sound wave is a measure of its ________.
A. loudness B. frequency C. period D. wavelength
When all of the statements in a high-level source program are translated as a complete unit before any individual statement is executed, the programming language is called a(n) ____ language.
A. machine B. procedural C. compiled D. interpreted