Part -1:
You are in charge of designing the database for a Computer Sales and Repair Store (CSRSS). The CSRSS includes a collection of tables and a form-based user interface to insert, update, and retrieve information about various services of this store, which includes computer sales and upgrades/repairs (through replacing parts and/or installing various software). Examples includes selling laptops and desktop computers, changing screens, keyboards, OS, power unit, anti-virus, upgrading main memory, hard disk, etc. There are a number of personnel working in this store each of which has a code when they login and record their activities. For each service or part provided there is a default cost, which is recorded in the system but could also be over written by the clerk for complications that may arise during the service. Depending on their seniority and agreement with the company (who owns the store), there will be different percentage of commissions. For instance clerk 1 may have agreed to collect 50% of service fees, so if he/she changes a hard disk, if the hard disk is $80 and replacing it is $30, then clerk 1 gets 50% of $30 and the company gets $15+$80 (assuming all parts and software are provided by the company).
The following are the requirements specified for the CSRSS application:
Details on sales and services provided: Basic information on all computers, parts and software, the purchase date and amount, etc. The store manager can check details of activities of each employee in terms of different services he/she provided, the dates, amounts as well as store revenue and each technician's shares.
For computers/laptops brought to the store for repair more than once, a history of previous service activities on the computer.
Details on employees: Records of information on each employee, date of employment, his/her seniority, weekly, monthly, and annual payments.
Inventory Details: The system is expected to contain complete and up-to-date information of computer and parts present or sold, with records of date, and amount purchased/sold.
Details on Online Sale: The store is also involved in online computer and laptop sales, say through EBay. Extensive report of online sales, locations delivered (City, Province, Country) and revenues. When these sales are done through one of the employees, there is an x% commission for that employee calculated in his/her income. The percentage x is recorded in the system for each employee and the default value is 50%.
With this information, do the following initial steps in your database design process:
1. Develop an E/R diagram to represent the conceptual database scheme for the CSRSS.
In the diagram, mark the various constraints (keys, cardinalities of the relationships, etc.). Identify any constraints that are not captured by the E/R diagram.
2. Convert your E/R diagram from 1 into a relational database scheme. Make refinements to your scheme if possible. Write the tables in the form R(A1, ..., An). Identify the primary keys (by underlining them) and the foreign keys in the relational schemes by indicating to which table/attribute they refer, and hence note the referential integrity constraints in the scheme. Indicate if there are other constraints depicted in the E/R model that you cannot (yet) describe in the relational model.
Part -2:
Relational Algebra
You are provided below a design of a relational database for a hospital. The database contains information on employees (doctors, nurses, and other staffs), patients, and medical services available, e.g., different departments, different labs in each department, different tests in each lab, number of wards/beds in each department etc. A medical file is opened the first time a patient is admitted to the hospital and updated on every visit or medical test in the hospital. Some information on how this hospital runs:
- Each department will be administrated by a doctor.
- Each department has many doctors, but each doctor work for one department.
- Each patient is assigned to a doctor upon his/her visit/admission.
- Different tests may be required for a single patient.
The database schema is as follows, where the underlined attribute(s) in each relation collectively form the primary key of that relation:
1. Department (did, deptName, administrator, numberOfBeds)
2. Employee (eid, did, firstName, lastName, jobTitle, startDate, lastDate, gender, dateOfBirthob, phone#, email)
3. Patient (medicareNumber, firstName, lastName, gender, dateOfBirth, phone#, address*)
4. Admission (date, MedicareNumber, AssignedDoctor, reasonForAdmission, dateAdmitted, dateDischarged)
5. Visit (time, date, MedicareNumber, DoctorId, diagnosis, medicalReport)
* address consists of civic number, city, postal code, and country.
Doctors and Nurses are special kind of employees for whom we need to record their specialties and departments. Each specialist has a "visit_fee" attribute. Looking at the following queries, you may introduce some minor changes to the design; we might have missed some attributes from some relations.
Do the proper refinements for the design to be able to answer the following queries.
Now, express the following queries in Relational Algebra (each query 4 Points):
1. List the information of all doctors who are specialized is heart surgery.
2. List the information of all nurses who are from Laval and started since June 01, 2012.
3. Given a patient's medicare number, list the Medical Report of that patient.
4. Given a patient's medicare number, find out how much s/he has paid for each visit since June 2014.
5. List heart patients who were admitted/visited at least twice.
6. List patient's first name, last name, phone, date admitted, date discharged for all admitted patients with Cancer or HIV.
7. List patient's first name, last name, phone, date admitted, date discharged for all admitted patients with Cancer and HIV.
8. List patient's first name, last name, phone, date admitted, date discharged for all admitted patients with Cancer but do not have HIV.
9. List patient's first name, last name, phone, date admitted, date discharged for all admitted patients who are doctors.
10. List employee's first name, last name, jobTitle, phone# of employees who are patients and diagnosed with HIV.