This is an individual assignment. All work must be your own. You should not look at any other student's work (in whole or in part, on paper or on screen), nor allow anyone else to look at yours, during the course of this assignment.
Upload to the CS Homework system on or before midnight on 10 (new!) October 2022. (old: 7 Oct) Typed, in a plain text, PDF or MS Word document.
(Typed homework, in all cases. DO NOT upload a photograph of handwritten scribbles.)
Consider the following relations:
Engineer (engineerID: integer, lname: string, fname: string, jobtitle: string,
email: string, proEngineerExamTitle: string, proEngineerExamPassDate: date, salary: integer, vacationDaysAccumulated: integer)
Project (projectID: integer, name: string, startDate: date, endDate: date, customerID: integer, monthlyBudget: integer, totalBudget: integer, managerID: integer)
ProjectEngineers (projectID: integer, engineerID: integer, hoursPerWeek: integer)
Customer (customerID: integer, name: string, address: string, phone: char(10),
contactName: string, contactPhone: char(10))
WorkingGroup(workgroupID: integer, workGroupTitle: string, workingOnStandard: integer, sponsorOrgName: string, startDate: date, endDate: date, annualBudget: integer)
WorkingGroupMembers(workgroupID: integer, engineerID: integer, startDate: date, endDate: date, role: string)
Standards(standardsID: integer, standardTitle: string, status: string,
approvalDate: date, replacesOldStandard: integer, price: integer)
The relations capture some basic information about an engineering consultant company. Engineers work on Projects for Customers.
Engineers have job titles, and some engineers have passed Professional Engineers exams in some specific field of engineering (civil, chemical, fire protection, etc).
Projects are performed for and paid by Customers, and customerID
in Project is a foreign key referencing Customer. Projects are managed by Engineers,
and managerID is a foreign key referencing Engineer:engineerID.
ProjectEngineers keeps track of which engineers work on which projects.
Both key fields are foreign keys to the appropriate tables. hoursPerWeek keeps track
of how many of the engineer's weekly hours are assigned to this project.
Customers are companies. Some employee at each customer company will be
the official contact for that company.
WorkingGroups are groups of people who draft new technical standards and
steer them through the standards approval process. A working group works on a standard (workingOnStandard is a foreign key
referencing standardID in Standards). And a working group has a sponsoring
organization. For example, in computing and electrical engineering, the IEEE sponsors a lot of standards development work, including
the WiFi standards you may be familiar with.
WorkingGroupMembers stores which Engineers are working on what WorkingGroups.
workgroupID is a foreign key referencing WorkingGroup.
engineerID is a foreign key referencing Engineer. Engineers start and stop work
on a working group on particular days. (Some may serve during all the years
the working group is active; some engineers might only serve for a few months.)
Working groups members may serve with
particular roles -- "Chair", "Vice Chair" and so on.
Standards stores data about standards. Standards might have different
status values -- "draft", "approved" and so on. (If the standard is a draft,
and not yet approved, its approvalDate will be null.) Some new standards are designed
to replace older standards, and replacesOldStandard is a foreign key referencing
standardID (in the same table) when one standard is intended to replace or
has replaced another. For example, the "IEEE Health informatics- Personal health device communication- Part 10419: Device Specialization- Insulin Pump" standard IEEE 11073-10419-2015
has been superseded (replaced by) IEEE standard IEEE 11073-10419-2017.
You may make the simplifying assumption that comparisons on "date" or "timestamp" fields will work with basic =, <, >, etc, boolean operators. This is not the case in many DBMS products; for real work you would need to consult the documentation for your particular DBMS software.
Write the following queries in SQL.