PROJECT $(30\backslash \%)$

Objective

Perform development and management of databases using SQL$.($A$5,$PLO$6)$

Instruction

This is an individual $-$based Project

Coursework Details

Independent Cinemas

The Independent Cinema group is a small group of independent cinemas in Hertfordshire. They show what they regard as quality or specialist films and they usually start each showing of a film with a short talk about the film by the cinema manager. Information about each film includes how long it runs for and its classification, plus a short description.

There are a number of cinema venues in Hertfordshire, each cinema has a unique name and the location and date it first opened are recorded. Some venues will have multiple screens of different seat capacities, but each screen only has one showing of a film in any evening so a venue can show as many films as it has screens each day. A specific film will be performed over a sequence of evenings on the same screen, the collective performances are known as showings.

Each cinema employs a group of staff with certain job types $($Administrator$,$ Receptionist, Cinema Manager, Projectionist, Accountant, Cleaner etc.$).$ Employees only work at one cinema. Personal details such as address, phone number, date of birth, and employment details such joining date and salary are retained about each employee. Each cinema only has one manager and some employees supervise other employees.

$[$Semester A $2024-2025($SOC$)]$

The group would like to know how much money was taken for each performance and showing of the film, and how many seats were occupied, so that they can determine how much money a film brings in overall from the different cinemas, and an indication of films popularity $($or not!$).$

The following E$-$R diagram has been constructed from this specification:

PROJECT

For Project, you are required to undertake the following:

$1.$ Write out the Relational Schema for each of the $6$ entities detailed

$-$ These should use the format as defined in this example $($not taken from the specification$)$ underlining the primary key attributes and marking the foreign key attributes with an asterisk.

Employee $($Employee Number, Name, Address, Department$*)$

$-$ Use the same names for the Relational Schema statements as are specified in the E$-$R Diagram.

$-$ Do NOT add or remove any attributes, specifically, do NOT replace composite Primary Keys with a Simple Primary Key

$2.$ Using your Oracle account, create the table structures as reflected in the diagram and your Relational Schema

$-$ Again, use the same names for the CREATE TABLE statements as are specified in the E$-$R Diagram these should not clash with any existing tables you have created so far, however, if you have created your own tables with the same name, rename them prior to starting this task.

$-$ Ensure that you encode the following:

$-$ All attributes, labelled as specified in the E$-$R diagram $($including underscores where used$)$

$-$ Suitable datatypes with sensible sizes and lengths, and reflecting the nominal or quantitative nature of the attribute

$-$ All PRIMARY KEY and FOREIGN KEY constraints

Note: the issue of two tables referencing each other MUST be resolved using an ALTER TABLE statement

$-$ Use NOT NULL constraints where appropriate $$

SEGi

$[4$COM$2004]$

University $8$

$[$DATA MODELLING FOR DATABASES$]$

Colleges

$[$Semester A $2024-2025($SOC$)]$

$[$Project$]$

$-$ Provide CHECK constraints where appropriate

Submission

All coursework should be submitted before or on the dateline, based on the provided guidelines and formats.

Dateline

All submissions are due on $[25$ November $2024].$ Late submissions will not be accepted. All submissions need to be presented individually in $[$Date for the presentation $($if related$].$

Deliverables

All submissions will be through the SEGi Blackboard. Any submission outside of the SEGi Blackboard will NOT be considered.

Important notes:

If missed out on any of the above, the submission will be regarded as INCOMPLETE. Hence, no further assessment will be carried out. The safety of the assessments is the responsibility of the student, so please ensure that no unauthorized personnel can access the work without permission.

Report Formatting

The report must be written using the following formatting:

NIL Grad$$ng

The assessment for the project will be evaluated based on the marking rubrics.

Marking Schema

Relational Schema:

$2$ marks per correctly defined relational schema, maintaining entity and attribute names and utilising underscore and asterisk notation

Create and Alter Table statements $($considering each table$)$

$1$ mark for conforming to table and attribute names

$2$ mark for each correct Primary Key specification

$3$ marks for each correct Foreign Key specification

$3$ marks for sensible datatype definitions

Create Table statements $($considering all tables$)$

$11$ marks for sensible NOT NULL constraint use

$20$ marks for creation of sensible CHECK constraints

If you fail to record your username on the submission document, $5$ marks will be deducted from your total.

If the tables do not exist in your ORACLE Account, NO marks will be awarded for the CREATE TABLE element