draw the ER diagram based on the business rules stated above. Ensure the many-to-many relationship is resolved.
Question:
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
Step 2: Design ER diagram
Using MySQL Workbench, build the ER model based on the drawing from step 1.
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
Step 3: Write DDL statements
Referring to the model created in Step 2, write the SQL DDL statements to perform the following:
- Create database 'Assignment1'
- Create all tables:
- Include primary and foreign keys
- Choose the datatype and length for each column as you see fit
- All primary keys, foreign keys must be specified in the CREATE TABLE statement
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 can enroll for any number of courses
- Each course can have any number of students
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
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George