PART II. CONCEPTUAL DATABASE MODEL (30%)

GeoTab Company has contacted you to make a database for their conference schedule and registration. A sample of a conference schedule is provided in the appendix. Through interviews with the manager, you obtain the following information:

GeoTab runs a conference named "Connect" yearly. The conference takes place on a specific date, and a specific location. The day and place of the conference may vary over years. GeoTab needs to keep track of the conference name, date (e.g. Jan 16, 2020), location (San Diego convention center) and the address (111 W Harbor Dr, San Diego, CA 92101, United States). The conference runs multiple tracks where each track has a specific topic's name (e.g. Government, Learning center track, Big Data) and a specific room number. One Track belongs to one and only one scheduled conference. Each track consists of multiple presentations. A presentation can belong to one and only one track. Each presentation has a title, start time and end time. Each presentation is presented by one or more presenters. A presenter presents one or more presentations. A presenter is identified with first and last name and company name. One the conference schedule provided in the appendix, some presenters names are followed by the company names in parentheses (e.g. Chulsu Ha (SOCAR)). When the company name is not mentioned, the presenters works for GeoTab.

Design a conceptual database model without redundancy for the above company. Draw the ERD using Microsoft Visio (choose Crow's Foot Diagram). Include the following in your assignment document:

- The ERD

- A list of business rules that you assumed for drawing your ERD (only if not clearly specified above). In the real world, you would go back to the manager to confirm if your assumptions are correct.

PART III. PHYSICAL DATABASE MODEL (20%)

- Write the SQL statements for creating the tables for the entities in your conceptual database model in the project SQL file. Note the implementation must match the ERD. The SQL statements include entity integrity and referential integrity constraints.

PART IV. DATA MANIPULATION

ENTER DATA (10%)

Enter the following data using SQL commands and save in your project SQL file.

- You are given a sample conference schedule in the Appendix. Enter the data shown in the conference schedule in the relevant table(s).

- Show the data in all your tables in your project document (a clear and readable screen shot)

UPDATE AND LIST DATA (40%)

For the following questions, include the SQL commands in your SQL file and show a clear and readable output of the results of each query.

1. Change the start time and end time for the presentation titled "Fleet Electrification process and how Telematics and the electric vehicle suitability assessment can help" to 9:00 and 9:30 respectively.

2. List all the presentations under the learning center track.

3. List all presentations that occur in room number 25 before 10am.

4. Create an SQL statement to list all presenters in the conference. Show the presenter first and last name and the company they belong to.

5. Create an SQL command to list all presentations (title) along the track number they belong to, track title and room number and its presenters. Concatenate each presenter first and last name together in one string.

6. Show the total number of presentations and the total number of presenters in the conference.

7. Show the room number and the number of presentations running in each.

8. Show the conference id and the number of tracks present in the conference.

9. Show the presenter and the number of presentations they have.

10. Show the presenter that has the most number of presentations.

APPENDIX

Conference Schedule Sample

Wednesday | January 16, 2020

Time Room #25

Learning Center Track Room #26

Big Data

8:30 Data Science Package

Dan Dodgson

9:00

9:30 Fleet Electrification Process and how

Telematics and the Electric Vehicle Suitability Assessment can help

Matt Stevens & Hani Hawari

10:00 Predictive Maintenance:

The electrical systems rating

Daniel Lewis,Chris Jackson (Pepsi/Frito)

10:30 Helping Fleets Succeed with their EVs

Megan Allen & Sarah Kerr