Part A

1. Your first task is to study the Case study requirements and gather as much knowledge to list all the main Entities and attributes.

2. Create an Entity Relationship Diagram (ERD) to help you decide on the relationships.

Your entity relation diagram that models your database design should:

i. Include all entities, relationships (including names) and attributes.

ii. Identify primary and foreign keys.

iii. Include cardinality/ multiplicity and show using crow’s feet or UML notation.

iv. Include participation (optional / mandatory) symbols if applicable.

The E-R should be created as part of a Microsoft Word document.  Hand-drawn diagrams will not be accepted.  It is recommended that you complete your ERD using Draw.io, Visio or try www.gliffy.com (Search for ERD gliffy to get started.).

3. Create relationships between tables and enforce the referential integrity as shown below.

Relationships:

• Student can enrol themselves into more than one Dance Class.  

• One type of Dance can have more than one classes timetabled and they can run at various locations

• The dance styles can be Jazz, Belle, Hip-hop, Salsa etc.

• Each room can be timetabled more than once as long as the classes don’t overlap.

• Each enrolment needs a payment that needs to be pre-paid, but a student is allowed to make part payments. Means they can pay the whole amount using different payment methods like cash, debit card, credit card etc.

4. The database should include suitable validation and integrity checks as well as appropriate referential integrity checks.  That is, AS A MINIMUM, your system should ensure that the following events cannot occur:

Referential Integrity Constraints:

• A class cannot have a student who is not registered means does not exist in the system beforehand.

• A student cannot be deleted from the system if a student is enrolled in a current dance class. Similarly an instructor cannot be deleted from the system if they are instructing a current dance class.

• An enrolment cannot be deleted if a payment has been made and dance class is allocated to the student. SDA does not cater any refunds.

• Only Dance styles entered in the database can be attached to a dance class. A new form of dance style for a dance class may be only added to the system after the required dance style is added first.

• Rooms cannot be deleted if there are dance classes already allocated to that room.

• Payment methods cannot be deleted once payment methods have been recorded against payments.