Exam timetabling database system

A. Design (25%) Topic for my database system development: Exam Timetabling database system 1. Describe the proposed system in words (max one page) 2. Develop a UML class diagram for your system - based on your diagram, briefly discuss, domain, super key, primary key, associations (relationships), multiplicity (or cardinality), in the context of your database system (i.e. give relevant examples) 3. Develop a Scheme based on your class diagram - with reference to your scheme, identify and describe, candidate key, primary key, foreign key, referential integrity, surrogate key and (if relevant) external keys 4. Write the SQL commands to create the database, create and populate tables, query individual tables. (You must include the SQL script in your document - cut and paste queries and results from mysql). Be prepared to defend your choice of data types for selected fields, and any 'not null' fields. Your design must include at least one many-to-many (M:M) relationship, and one 1:M relationship. B. Queries (25%) Write SQL Queries to interrogate your own database. In each case you must write in English what each query does followed by the appropriate SQL Query. Show the output also. ie. 1. Show the names of all teenagers. select firstName, lastName from students where age >= 13 and age <=19; firstname lastname Jack Pale Jill Hill You suite of queries must include at least one query involving each the following: SELECT all columns, select subset of columns, ORDER BY, DISTINCT, GROUP BY, LIMIT, WHERE clause, logical operators AND/OR, a selection of 5 functions (e.g sum(), ucase(), INNER JOIN, LEFT JOIN). INSERT, DELETE, UPDATE. (Portfolio Stage 2) 50% C. Use-Cases (25%) Identify 4 use-case scenarios that involve transactions that are applicable to your database. Sketch the user interface that might be used for each transaction - identifying fields that are entered by the user, and those filled in by the software system. Wherever data is retrieved from or inserted (updated or deleted) from the database, you must write the appropriate SQL Query. Briefly describe what the software system does (e.g. get current date from computer system to insert into DATE field of books loan database). (Low marks for all trivial queries) Examples of transactions: a. Simple - add a new customer to the Customers table b. Place an order ( customer ID to be obtained, order details to be entered, orders table INSERT) c. Calculate total cost of job X by employee. d. Change a flight schedule. D. Indexing (10%) Implement appropriate indexes in your database, explaining why the indexes were selected. Briefly discuss the pros and cons of indexing in the context of your database. This should form a section in your Portfolio. (A minimum of three indexes are required.) E. Views (15%) Create at least TWO views on your scheme, and explain why they are appropriate for your scheme. Query the views to demonstrate their use. 3. Java Database Connectivity Implement sample Java code to read data from your database table(s) and display the data on screen. Give a brief description of what you have done and include your code in the document. (Java code to be used on Net beans) Deliverables A. Portfolio Document A.1 - One .pdf document, with Table of Contents, containing all code, diagrams and descriptions. Include your name and class, module name, and date at the top of document. A.2 - One .SQL Script (text file) containing all the SQL Script to create, populate and demonstrate your database. (This should execute with no errors.) A.3 – Java Project Source code.