Assignment Title $$ Modelling $$ College

This assignment relates to the following Course Learning Requirements:

$$ CLR $1$: Follow lab policies and procedures for etiquette, software licensing requirements and lab

submissions

$$ CLR $2$: Relate knowledge of design theory to provide solutions in response to business database software

deveopment proposals

$$ CLR $2$: Explore basic Entity Relation Diagram $($ERD$)$ components $$ entities, attributes, relationships

$$ CLR $2$: Define key, candidate key, prime key, foreign key, surrogate key, and determinant

$$ CLR $2$: Communicate solutions electronically, in written and visual form

$$ CLR $2$: Differentiate between identifying and non$-$identifying relationships

$$ CLR $2$: Use workplace tools to model database design using ER diagrams

$$ CLR $3$: Map an ERD to a Relational Database

$$ CLR $4$: Use Data Definition Language $($DDL$)$ to add, modify and delete database objects, i$.$e$.,$ tables, views,

indexes

$$ CLR $4$: Choose appropriate data types to create table

$$ CLR $4$: Enforce entity integrity and referential integrity by choosing primary and foreign key constraints

$$ CLR $6$: Create and manipulate database objects

$$ CLR $8$: Research a topic on sustainability and build a database

Objective of this Assignment:

Fictional Situation: You have been given a contract to develop a database design for a new college. The college

currently has a single campus, but two more campusses are expected to be operational within the next $5$ years.

In this assignment, you will develop a model using MySQL Workbench and manually create the necessary DDL

statements to build tables for this new college environment.

Pre$-$Assignment Instructions:

$1.$ Complete Modules $1-6$

$2.$ Complete Labs $1-5$

Business Rules:

These business rules must be reflected in the model. Pay close attention to the wording of the rules to ensure the

correct cardinality is used.

$$ A college must have at least one campus

$$ Each campus is tied to a single college

$$ A campus has many departments, and each department reports to a single campus

$$ A department offers many courses

$$ Each course is tied to one department

$$ A student must enroll in at least one course

$$ Each course must be attended by at least one student

$2$

Data To Be Recorded:

$$ Primary keys

$$ Foreign keys

Include the following data in your model, include any other columns you deem appropriate:

$$ Department name: mandatory

$$ Department location: mandatory

$$ Course code: mandatory

$$ Course duration: mandatory

$$ Course name: mandatory

$$ Student number: mandatory

$$ Student first and last names: mandatory

$$ Student phone number: optional

Assignment Tasks:

Step $1$: Draw draft of the ERD:

On paper, draw the ER diagram based on the business rules stated above. Ensure the many$-$to$-$many relationship

is resolved. The diagram must include the entity names, relationships, and cardinalities only. Do not include

column names. Note: Review the business rules carefully so they are being correctlly reflected in the model.

On the diagram, identify the three entities involved in the resolution of the many$-$to$-$many by placing an asterisk next

to the entities.

Take a photo of your solution. Make sure the entire model is visible and legible.

Step $2$: Design ER diagram

Using MySQL Workbench, build the ER model based on the drawing from step $1.$ Refer to Lab $3$ on how to create a

model in Workbench.

The diagram should include:

$$ Entities

$$ Attributes within entities

$$ Relationships between entities

$$ All relationships should be non$-$identifying except for those involving the assocative$/$junction table, which

should be identifying relationships

$$ Cardinalities

$$ Primary and Foreign keys

$$ Ensure each table has no duplicate columns $($pay close attention to the Workbench icon used to define

the relationships

Create an image of the model $($File $->$ Export $->$ Export as PNG$)$

Step $3$: Write DDL statements

Referring to the model created in Step $2,$ write the SQL DDL statements to perform the following:

$$ Create database $$Assignment$1$

$$ Create all tables:

o Include primary and foreign keys

o Choose the datatype and length for each column as you see fit

o All primary keys, foreign keys must be specified in the CREATE TABLE statement

All DDL statements must be written manually. Do not forward$-$engineer the SQL from MySQL Workbench