CO532留学生代写、代做SQL编程语言、SQL代写、代做Data Model

日期：2019-03-23 11:16

CO532 Assessment 3

Here is one solution to Assessment 2

Conceptual Data Model

Assumptions

A student may have attended no training session

A chef may not have supervised a training session

A student may have not yet cooked for an event

At least one friend needs to attend the cook-and-dine event

A kitchen can be used by 0 or more sessions.

A kitchen can be used by 0 or more events.

Relational Schema

Primary keys are underlined and foreign keys are in italic. No foreign keys are allowed to be null.

Student(studentId, name, streetAddress, city, postcode, phoneNum)

TrainingSession(date, time, buildingName, roomNum, studentId, empId)

CookAndDineEvent(date, time, grade, buildingName, roomNum, studentId)

Kitchen(buildingName, roomNum)

Chef(empId, name, phoneNum, email, qualification)

Friend(friendId, name, email, dietaryConstraints)

EventAttending(friendId, date, time, buildingName, roomNum)

Constraints

Unique Constraints:

TrainingSession

(date, time, studentId)

(date, time, empId)

CookAndDineEvent Unique Constraints

(studentId)

Domain Constraints:

grade: {'A', 'B', 'C', 'D', 'E', 'F'}

qualification: {'certificate', 'associate degree', 'bachelor', 'master', 'none'}

Not Null Attributes:

Student: name

Friend: name

Chef: name, phoneNum, email, qualification

TASK 1 (40%)

From the above information, implement the database tables in PostgreSQL, choosing sensible data types for each

column. Ensure you maintain referential integrity. Implement all the above domain constraints and unique columns.

Add appropriate not null constraints to attributes. Ensure your data types and constraints allow for sensible future

data to be added to the database in the future.

Insert the following data into the tables. Do not add any data except this.

Student

studentId Name streetAddress city postcode phoneNum

1 Sue Faffer 12 Meryl Street Canterbury CT6 8UJ +44 1227-968-5287

2 Chris Driver 3a Carla Lane Herne Bay CT4 9KL NULL

3 Olive Kemp 1 Cecil Road Canterbury CT6 8UJ 01227871000 extension 105

4 Sue Faffer 14 Meryl Street Canterbury CT6 8UJ 1227943979

TrainingSession

Date Time buildingName roomNum studentId empId

10/01/2019 10:00:00 farm house 2 1 1001

11/01/2019 12:00:00 annex 1 1 1001

12/01/2019 10:00:00 annex 1 1 1002

13/01/2019 12:00:00 farm house 2 4 1003

05/02/2019 10:00:00 farm house 1 1 1003

05/02/2019 10:00:00 farm house 2 2 1002

05/02/2019 10:00:00 annex 2 3 1001

CookAndDineEvent

date Time Grade buildingName roomNum studentId

06/02/2019 18:00:00 A annex 1 4

05/02/2019 12:00:00 B farm house 2 1

03/05/2019 12:00:00 NULL annex 1 2

Kitchen

buildingName roomNum

annex 1

annex 2

farm house 1

farm house 2

farm house 3

Chef

empId name phoneNum email qualification

1001 Sally Smith +44 1227 871 194 sas777@live.com certificate

1002 Ben Driver 0779 601423 benny910@gmail.com none

1003 Jane Smith 01227 8812061 jane814@live.com certificate

Friend

friendid Name Email dietaryConstraints

2 Yasser Crimp y.crimp681@gmail.com Allergic to Nuts

1 Jane Smith NULL NULL

3 Ben Blimp bb@live.com None

5 Chris Driver cd615@kent.ac.uk No

4 Jim Smith smithy1972@gmail.com Gluten free only.

EventAttending

friendId date Time buildingName roomNum

2 03/05/2019 12:00:00 annex 1

4 03/05/2019 12:00:00 annex 1

1 05/02/2019 12:00:00 farm house 2

2 06/02/2019 18:00:00 annex 1

3 06/02/2019 18:00:00 annex 1

TASK 2 (10% Each query)

Answer each of the below with a single PostgreSQL query. For queries that return information, return only the data

specified and use only the information provided in the question: the queries must utilize the information given, do not

hard code ids or other information that is not explicitly given. Ensure the queries will work with any future data, not

just the data given.

2.1) List the Chef name, phoneNum, date and time for all TrainingSessions. Sort alphabetically by Chef name first, then

earliest in terms of date and time first.

2.2) For each student with training sessions in the farm house, give the number of such training sessions, student

name and studentId.

2.3) Give the student name, dietaryConstraints, date and time for all cookAndDineEvents that Yasser Crimp will be

attending.

2.4) Give the Student name, TrainingSession date and times for all students who do not have a CookAndDineEvent.

2.5) List the date, time, student name and studentId for all activities (both Training Sessions and CookAndDineEvents).

2.6) For the TrainingSessions with all Chefs who are qualified with a certificate and which are in farm house roomNum

2, change the location to be in farm house roomNum 3.

Submitting

Submit via Moodle. The deadline is given there.

Submit one file in plain text format. It must have a .txt extension.

In the file you should provide:

The PostgreSQL commands that you used to create the tables (in the order you created them).

The PostgreSQL commands that you used to insert the data (in the order you inserted the data).

The PostgreSQL commands to run the queries.

Code that does not work will get 0 marks. Any non-executable text must be in comments, for example, either on lines

starting with “--” or between java-like /* … */ blocks. Put a comment indicating the question number before

each query from Task 2 (e.g. “-- 2.1”).

Late submissions will get 0 marks.

I will not be available on the day of submission for questions, make sure you get any queries to me before then.

Peter Rodgers