Question

Database design

Part One (Conceptual Database Design) As a database developer, you have been asked to design a new database the operation of the new project within the Liverpool John Moores University (LJMU). LJMU i planning to deliver a number of professional training courses in Al. Each course is delivered by one member of staff at LJMU's Training Centre; each staff is allocated a staff number. The policy of LJMU is to record the start working date of each staff, the first and last name, the date of birth, home address, telephone number for (home and mobile email, type of contracts (permanent, sessional), sex, National Insurance Number and the specified salary. There are different training courses offered by LJMU. The fees vary for each course and on the number of delegates a company sends. For example, if a company sends one delegate, the charge may be £2500. If the company sends two people, the first may be charged £2500, but the second may be charged £2000 The Al professional courses can be attended by a number of delegates, subject to a limit for each course (Maximum 10 for each of the planned professional courses). A start and end date is assigned for each course with the start and end time. An invoice is sent to the delegate's company. The invoice clarifies to the client orland the company the date that the fee is paid, method of payment (one full payment, or a deposit then completing the payment) (a) Identify the main entities (list all attributes of these entities) for the above proposed database. Determine primary key, Candidate keys and foreign keys attributes for each entity ) Create a single Entity-Relationship (ER) diagram to represent the data requirements of the database (Conceptual Database Design). To simplify the diagram, only show entities, relationships, primary and foreign key attributes. State any assumptions (if necessary) to support your design. Identify the main relationships between entities described in part (a); represent each relationship in your ER diagram, clearly labelling each relationship. State the degree of each of the relationships described in part (b). Decompose any many to many relationships, replacing them with new entity types and relationships.

Answer 1

PK-->PRIMARY KEY

FK-->FOREIGN KEY

STAFF First Name Last Name Address LandLine Mobile Number DOB VARCHAR(15) CHAR(15) ARCHAR(30) INTEGER(20) COURSES PK Course ID INTEGER(20) DATE Teaches assigned Staff Course Name VARCHAR(15 VARCHAR(15) PK National Insurance NumberVARCHAR (15) FK Staff ID Sex Salary email Contracts Type DOJ VARCHAR(10) INTEGER(30) VARCHAR(15) VARCHAR(20) DATE VARCHAR(15) delivered by Fees NUMERIC(15) ates Number INTEGER(15 PK Staff ID attendend By Admin Generates Invoice DELIGATES VARCHAR(15) VARCHAR(15) VARCHAR(15) First Name Last Name Company Name VARCHAR(15) Start Date and Time DATETIME produced by PK Emp ID FK Company ID INVOICE INTEGER(15) PK Invoice Number INTEGER(10) FK|Company ID |INTEGER(15) VARCHAR(15) End Date & Time DATETIME FK Emp ID Payment Date DATE Pa t Mode VARCHAR(40) send for training invoice of all the deligates COMPANY Received By Company Name VARCHAR(30) INTEGER(15) VARCHAR(30) VARCHAR(15) invoice send PK Company ID Address FK Emp ID Contact Number