Note: While it is understood that groups may share their expertise and communicate with each other, each group's work must reflect sufficient individual group effort to merit its grade. Sharing complete solutions with other groups is not acceptable. Such blatant collusion will result in a grade reduction for both groups and in extreme cases could result in a zero and a charge of academic misconduct.

This can be a group assignment. Group size is 1 to 4 maximum. No exceptions. 

Read and understand the scenario carefully.

The TMU (Toronto Metropolitan University) Walkin Medical Centre needs a database to keep record of and provide information about patient visits, past, present and future. Where an appointment reservations is made it is never for a specific physician. The patient will see whatever physician is available when they arrive. For each patient we need to retain the patient's health card number, name, address, and gender, date of birth and telephone number.

A patient may see any of the physicians on staff but would only see one on each visit. A patient only is permitted one visit a day. The centre is very large and employs many physicians. Each physician has an identifying number and in addition information is kept as to their name, specialty and office location. The data integrity for the information recorded about the specialty of the doctors is also very important but not every doctor will have a specialty. Each specialty has a code and a description. After each visit the total fee incurred by the patient for that visit is recorded and as this is a private facility payment must be made immediately. The method of payment,(cash, cheque, or credit card) is also recorded. On each visit a patient may receive several treatments that involve the use of medical items that are stocked by the Centre. Each such use of a medical item is recorded with the quantity used and a comment. Information for each stocked item consists of an item number, a description, item type, normal price, the number of units in stock and unit of measure.

Certain treatment items due to their nature (e.g. inoculation, enema) may need to be applied by a nurse and where this is the case the identity of the specific nurse administering this treatment will also be recorded. Not all items require a nurse however all stocked items have been used at least once. Information pertaining to nurses include their id number, name and gender. Some nurses supervise other nurses. These relationships should be included in the model.

Part 1:Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify the different entities, the attributes of each entity including the Primary key, and the relationship between the entities. (100 marks)

Part 2:Write the SQL statements of the following: (100 marks)

2. Using SQL Server construct a database called Project2_GRP## (where ## represents your group number: ie 01, 02, etc)
4. Define all primary keys, foreign keys and data types (20 marks)
6. Populate the tables with sufficient data (at least five records to each table) to adequately test all aspects of your queries. (See instructions below). Failure to do so will lose you marks
8. Develop the SQL queries to satisfy the below - 80 marks.

• at least three different SQL SELECT statements with WHERE clauses that will show different result.
• one SQL statement that uses any one of the aggregate functions with group by clause.
• one update statement that will modify the data inserted in task C.

Note:Queries that do not return sufficient data will be marked as wrong

2. All queries must generate some output
4. Queries with explicit sequencing must return a minimum of 3 rows
6. An aggregate query must have at least one row of output that is using at least 2 input rows (e.g. a count of 2, not 0 or 1)
8. Use column aliases where appropriate.
10. Suppress duplicate rows where appropriate for output based on your actual data even if not specifically requested in the question.

Have one group member submit a Zip file (not RAR) thru D2L that will contain the following files:

i) A text file containing names and student ids for the group.

ii) The mdf and log files of the database as modified with your test data.

(Note: to copy or zip these database files they must be detached or offline)

iii) The Sql file containing your answers (with the questions) to the queries.

iv) A Word document showing a screen shots of any SQL ERD, query and results of the first few records.