ICT702 Database Design and Implementation

ICT702IntroductiontotheRelationalDatabase Assessment 2 T1-25

Assignment2-IndividualAssignmentDatabase Design and Implementation

PurposeandLearningOutcomesPurpose

Thepurposeoftheassignment isto provide studentswith theopportunityto apply knowledge

andskillsdevelopedduringthesemesterwithreferenceto:

1. Develop a Case Study
2. Interpretation of business rules from a case
3. Conceptual data modelling through the creation of an Entity Relationship (ER)

TimelinesandExpectation

Thepercentagevalue ofthis Assignment is10%of thecoursemark.

Due:theduedateisonSunday13/4/202523:59week6,Refertothecourseoutline. Minimum Time Expectation: 15-20 hours.

CaseStudyRequirements

1. Scenario choice: your chosen scenario must include at least Ten to Twelve entities that are related to each other. This can be anything you choose, but you are advised to choose something you know, to make the exercise easier. An example could be a hobby you have perhaps you enjoy a sport, and you wish to develop a database that models some form of performance related to the teams and games played in this sport. Another example could be a part-time job, and you may wish to develop a database to assist some aspect of that Another example could be some area of science, perhaps an engineering-type application thatmodels a power station or a factory or an experiment of some kind. Because this brief is very open-ended, you are recommended to check your chosen scenario with the teaching staff to make sure it is suitable. A final consideration when thinking of your scenario is the list of SQL queries that you will need to execute within your database to prove the creation of tables and insertion of data. For instance, you will need to include certain search and statistical functions, so your scenario will need to be sophisticated enough to make these queries possible. PLEASE NOTE: each student is required to develop a UNIQUE scenario it will not be permitted for two students to use the same scenario.

2. Your ER Diagram must includeTen to Twelve entities. There is no upper bound on how many entities you choose, but you are advised to include no more thanFifteen. Design your ER Diagram with all entity names, attribute names, primary and foreign keys, relationships, cardinality, and participation indicated. You will need to normalize all your entities, to resolve any many-to-many relationships.

Observe the following restrictions when creating your scenario:

• Include a specialization hierarchy, with super types and overlapping or disjoint sub-
• Includeanexampleofbothcompositeandsurrogateprimary
• One(ormore) ofyour entities must haveanumeric
• One(ormore) ofyour entities must containanalphanumeric(varchar)
• One(ormore) ofyour entitiesmust containaDate
• Your attribute names and primary and foreign keys should be indicated as per the conventions given in the lecture slides (i.e. attributes as proper nouns,primary key underlined, and foreign keys in italics.
• All many-to-many relationships should be resolved, and you may wish to include a discussion of normalisation (Functional dependency Diagrams), also including the normal form that each entity is in and why that is optimal.
• For each entity, you must create some example data for that entity. Include at least 5 rows of data for each entity.
• DataDictionaryneedstobeprovidedbefore creatingthetablesandinsertingthe
• Write the SQL statements to create the table structures from your developed ERD. The structures should contain the attributes specified in your ERD. Use data types that are appropriate for the data that will need to be stored in eachEnforce primary key andforeignkeyconstraintsasindicatedbyyourERD.WritetheSQLcodetoinsert your data into the SQL table structures.

AssignmentRequirements

Detailed RequirementsFor the Draft Assessment, Students are expected to develop and provide their own case study, and then provide the following:

1-CaseStudy

• Entities Name
• Attributes
• Entity Relations
• Weak andStringEntity
• CardinalitiesandConnectivity
• Optionaland Mandatory Relationships
• Primaryand Foreignkey