University of Westminster Computer Science & Software Engineering
REASSESSMENT COURSEWORK SPECIFICATIONS
5COSC002W DATABASE SYSTEMS REASSESSMENT COURSEWORK
(2019/2020)
Module leade
Francois ROUBERT
Unit
Database Systems Coursework – REASSESSMENT COURSEWORK
Weighting:
60%
Qualifying mark
30%
Description
Produce a conceptual data model & a logical data model following given specs. Write SQL statements to complete specific tasks. Produce supporting documentation through a professional report.
Learning Outcomes Covered in this Assignment:
· LO1 design a data model using standard EERM constructs and convert this model into a relational database schema;
· LO2 use UML notations to produce a design that would encompass procedural aspects of a data management system;
· LO3 apply normalisation up to BCNF to a set of relational database tables;
· LO5 produce SQL queries to retrieve information from one or more tables;
· LO6 write a simple application program in a procedural language that interacts with a relational database.
Handed Out:
Monday 15 June 2020
DUE DATE
MONDAY 13 JULY 2020 at 13:00:00 – Part A + Part B
DELIVERABLES
FINAL REPORT: Part A + Part B: PDF Report in PDF, font Cali
i size 11
· 1 cover page, student details
· 1 side featuring conceptual ERD
· 4 data dictionary tables supporting conceptual ERD
· 1 side featuring logical ERD
· 2-3 sides featuring step-by-step guide for logical ERD
· SQL code (DDL) for creating 3 tables and screenshots of structures of the tables
· SQL code (DML) for inserting records into 3 tables and screenshots of the content of the tables
SUBMISSION
Online in ‘SUMMER 2020 REASSESSMENT’ section on Blackboard.
Type of Feedback and Due Date:
Online feedback and marks 15 working days (3 weeks) after the submission deadline.
All marks provisional until formally agreed by Assessment Board.
BCS Accreditation Criteria
2.1.1 Knowledge and understanding of facts, concepts, principles & theories
2.1.2 Use of such knowledge in modelling and design
2.2.1 Specify, design or construct computer-based systems
2.3.2 Development of general transferable skills
3.1.3 Knowledge of systems architecture
3.2.1 Specify, deploy, verify and maintain information systems
Assessment regulations
For detailed information regarding University Assessment Regulations on how you are assessed, penalties and late submissions, what constitutes plagiarism etc. please refer to the following website: http:
www.westminster.ac.uk/study/cu
ent-students
esources/academic-regulations
Penalty for Late Submission
If you submit your coursework late but within 24 hours or one working day of the specified deadline, 10 marks will be deducted from the final mark, as a penalty for late submission, except for work which obtains a mark in the range 40 – 49%, in which case the mark will be capped at the pass mark (40%). If you submit your coursework more than 24 hours or more than one working day after the specified deadline you will be given a mark of zero for the work in question unless a claim of Mitigating Circumstances has been submitted and accepted as valid.
It is recognised that on occasion, illness or a personal crisis can mean that you fail to submit a piece of work on time. In such cases you must inform the Campus Office in writing on a mitigating circumstances form, giving the reason for your late or non-submission. You must provide relevant documentary evidence with the form. This information will be reported to the relevant Assessment Board that will decide whether the mark of zero shall stand. For more detailed information regarding University Assessment Regulations, please refer to the following website:
http:
www.westminster.ac.uk/study/cu
ent-students
esources/academic-regulations
Coursework Specifications Part A: Conceptual ERD
[35 Marks]
Part A Project Brief: AKKTIVATION
AKKTIVATION is a large leisure and fitness company that has
anches all around the South East of England. AKKTIVATION’s mission statement is to give the opportunity to everyone to get active and fit in a stimulating and supportive environment. To this effect, AKKTIVATION provides first-rate facilities for people to undertake fitness training on their own, to take fitness classes in groups, or to just simply enjoy swimming in high-quality pools. Every AKKTIVATION
anch features a swimming pool (25 metres long with 6 lanes), a fitness suite (i.e. a standard gym with specialised fitness equipment for working out), and several fitness studios (i.e. typically wooden-floored rooms with mi
ors specially dedicated to fitness classes).
General customers can use the gym and the swimming pool as they wish, in a very straight forward manner. They just need to set up a payment balance with AKKTIVATION and every time they come, their attendance to a session is registered and their payment balance is debited by the price of a session. To credit their balance, they just need to pay in person at the counter. This way, they can swim in the pool or work out in the gym at their best convenience, in the simplest possible manner.
In order to benefit from additional exclusive services, customers can sign up for an AKKTIVATION membership and become members. AKKTIVATION members are required to pay a membership fee and to provide data about their physical characteristics (such as height, weight, existing conditions, etc.) and can set up fitness objectives, if they wish to. The key advantage from being an AKKTIVATION member is essentially the ability to book sessions in any AKKTIVATION
anch. Members can place bookings for three types of sessions: fitness classes (such as yoga, pilates, body balance, Zumba, etc.), swimming lessons (with an instructor, to learn how to swim or improve your swimming) or one-to-one training sessions in the gym (with a personal trainer, to work on a particular aspect of your fitness). If members want to benefit from one-to-one training sessions, they must first book a one-to-one induction session: it is an introductory session during which a personal trainer evaluates the member’s needs, show them how to do specific exercises, and authorises the use of special equipment. The special equipment that is authorised after each one-to-one induction session needs to be carefully recorded.
Regarding the staffing of AKKTIVATION personnel working locally at
anch level, there are essentially three categories. First, personal trainers conduct one-to-one training sessions in the gym to support the training regime of members and educate them about fitness (and therefore also conduct one-to-one induction sessions). Second, the role of support staff is to maintain the facilities and particularly the equipment available in every
anch to make sure it is in pristine condition. And finally, the third group consists of instructors who deliver booked sessions: swimming instructors give swimming lessons in the pool and fitness instructors teach fitness classes in the fitness studios.
Part A Questions
You have been hired by AKKTIVATION as a database architect to undertake a database project to support the data needs of the company. Your job in this first part is to produce a high-quality CONCEPTUAL ENTITY RELATIONSHIP DIAGRAM (ERD) and to produce a data dictionary to document and support your model.
⚠️ Prefix the names of all entities and attributes with your id number starting with w.
1) Produce a complete CONCEPTUAL ERD for AKKTIVATION.
It needs to include all the entities, relationships, multiplicities, attributes and primary keys that you have identified and needs to fit on one page of the report.
2) Create a Data Dictionary to document how you identified the entities for AKKTIVATION. To achieve this, fill in the 2 tables below to summarise and explain how you identified the entities for your data model.
Entity name
Description
General entity
Specialised entity
Explanation
For more information, please refer to page 510 of the 6th edition of the Connolly’s textbook.
3) Create a Data Dictionary to document how you identified the relationships and multiplicities for AKKTIVATION. To achieve this, fill in the table below to summarise and explain how you identified the relationships and multiplicities for your data model.
Entity name
Multiplicity
Relationship
Multiplicity
Entity name
Justifications for the multiplicity
(4 statements for each relationship)
For more information, please refer to page 513 of the 6th edition of the Connolly’s textbook.
Create your own separate table if you identify complex relationships to identify the entities involved and provide justifications.
4) Create a Data Dictionary to document how you identified the attributes and primary keys for each entity for AKKTIVATION. To achieve this, fill in the table below to summarise and explain how you identified the attributes and primary keys for your data model.
Entity name
Attributes for this entity (include PK)
Justification
For more information, please refer to page 516 of the 6th edition of the Connolly’s textbook.
Interactive FAQ
In order to provide you with the support you may require as a Database Architect and answer any questions you may have about the AKKTIVATION
ief, an interactive FAQ is offered on padlet, a Web tool to build a virtual wall / bulletin board. This allows you to ask specific targeted questions to the AKKTIVATION Managing Directors about the AKKTIVATION business so that to improve and refine your conceptual ERD and for these questions and their answers to be shared with the entire class. The padlet is available on https:
padlet.com/Francois_Roubert/AKKTIVATION_FAQ
Part A Marks Allocation
Part A will be marked based on the following marking criteria:
Criteria
Mark per component
Co
ect identification of entities + data dictionary tables
10
Co
ect identification of relationships + data dictionary table
10
Co
ect identification of multiplicities + data dictionary table
10
Co
ect identification of attributes and primary keys + table
05
PART A TOTAL
35
Coursework Specifications Part B: Logical ERD, SQL & PHP
[65 Marks]
Part B Project Brief: AudioVizzion
AudioVizzion is a large retail chain that offers optical and audiology services and essentially sells spectacles and hearing aids to the public. AudioVizzion operates globally from a large number of
anches located all around Britain. Every
anch offers AudioVizzion customers the opportunity to get tested on their vision and/or on their hearing before being able to purchase what the company refers to as Visual Devices (i.e. frames and lenses for glasses) and/or Hearing Devices (i.e. hearing aids).
Part B Questions
You have been hired by AudioVizzion as a database architect to undertake a database project to support the data needs of the firm. In this second part, you are given a conceptual data model for AudioVizzion (figure 1) and your first goal is to map it onto a high-quality LOGICAL ENTITY RELATIONALHIP DIAGRAM (ERD) to logically represent how the key business data needs can be organised as a set of inte
elated tables that can then be implemented. These tables need to be interconnected according to the strict rules of the relational model to be implementable. You also have to create tables in SQL and insert some data in these tables.