Assignment

Download as pdf or txt
Download as pdf or txt
You are on page 1of 4

ANSWER ALL QUESTIONS

a) Production tracking is important in many manufacturing environments (e.g., the


pharmaceuticals industry, children’s toys, etc.). The following ER diagram captures
important information in the tracking of production. Specifically, the ER diagram captures
relationships between production lots (or batches), individual production units, and raw
materials.

i. Convert the above ER diagram into a relational database schema. Be certain to indicate
the primary keys and referential integrity constraints.

b) Consider the following information about a university database:


• Professors have an SSN, a name, an age, a rank, and a research specialty.
• Projects have a project number, a sponsor name (e.g., NSF), a starting date,
an ending date, and a budget.
• Graduate students have an SSN, a name, an age, and a degree program
(e.g., M.S. or Ph.D.).
• Each project is managed by one professor (known as the project’s principal
investigator).
• Each project is worked on by one or more professors (known as the project’s
co-investigators).
• Professors can manage and/or work on multiple projects.
• Each project is worked on by one or more graduate students (known as the
project’s research assistants).
• When graduate students work on a project, a professor must supervise their
work on the project. Graduate students can work on multiple projects, in which case
they will have a (potentially different) supervisor for each one.
• Departments have a department number, a department name, and a main office.
• Departments have a professor (known as the chairman) who runs the department.
• Professors work in one or more departments, and for each department that they work
in, a time percentage is associated with their job.
• Graduate students have one major department in which they are working
on their degree.
• Each graduate student has another, more senior graduate student (known as
a student advisor) who advises him or her on what courses to take.

i. Design and draw an ER diagram that captures the information about the university. Use
only the basic ER model here; that is, entities, relationships, and attributes.

ii. Indicate any key and participation constraints.


c) Consider the table structure below and answer the SQL questions that follow:

TABLE CUSTOMERS

i. Create the table CUSTOMERS.


ii. Having created the table above, enter the first three records into the table.

iii. Return the name of the customer having a salary of 10,000.


iv. Remove the CUSTOMER table from the database.
d) Consider the following tables: Children, Playgroups, Activities. The Children tables
contains data about children (names, ages and addresses of parents) - we assume for
simplicity that names are unique, Playgroups says which child is in which playgroup and
Activities says what children in the playgroup did on a certain date (for example,
went to a zoo).

Children
Name Age Address

Playgroups
PlaygroupID Name

Activities
PlaygroupID ADate Description

Write SQL queries to do the following:


i. Find a list of names of all children aged 6.
ii. Find a list of Address of all children.
iii. Find names of all children who are in the same playgroup as a child called `Dancun
Obama'.
iv. Return a list of names of all children in the playgroup with PlaygroupID equal to 3.

You might also like