5CI017 DATABASE SYSTEMS RESIT For all resit students, or those with mitigation, to complete your Portfolio, you must complete the coursework below. Resit students: You must complete the coursework below only. The mark for this will replace the part of the portfolio that you did not pass, or complete. Note, the overall mark will be capped to 40% Mitigated students: If you have not completed either the coursework, or TCA previously, then you must complete the coursework below, which will make up 80% of your Portfolio. You must also attempt all the quizzes on WOLF, which will make up the remaining 20% TOPIC SELECTION Below is a list of topics, pick one from this list and carry out the tasks outlined below. Note, you will work individually on this topic. You must also pick a topic that is different to any previously carried out. 1. FELIX PETSITTERS – company provides care for customers pets for short or long periods of time. Pet boarding. 2. ABC DRIVING SCHOOL – Driving tuition for learners and advanced courses for drivers. 3. DIZZY SCHOOL OF DANCE – training in Performing Arts Company. Putting on local and national shows. 4. Inventory Control Management - Maintain a proper variety of required items, including location, transactions(buy and sale) and customers etc. 5. Student Record keeping System - a simpler E-vision, with your improvement. 6. Online Retail Application Database - Customers registers to purchase items. 7. College Database Project - A college contains many departments, and offer courses to students. 8. Railway System Database - A railway system, models stations, journeys, passengers and trains etc. 9. Hospital Management System - Records doctors, patients, appointments and diagnosis etc. 10. Library Management System - Records readers, books and borrowing and return records etc. 11. Payroll Management System Database - Records employee, salary, records and departments etc. 12. Health Care - An organization offers health care to patients upon diagnosis in various regions. 13. Restaurant Management Database - To maintain the catalogue for the list of food and beverage items in order to service customers by staff. 14. IT Training Group - The company has instructors providing training sessions to trainees with various courses. 15. Blood Donation System - Both involve staff and donors, and records donation process. 16. Art Gallery Management Database - Keeps information about artists, the art work and exhibitions. 17. School Management System - Maintains information about school staff, students, rooms assigned to classes etc. 18. Wholesale Management System - Maintain the details of stock, buy and sales etc. 19. Salary Management - Maintains employee salary and bonus etc., involving history. 20. A Personal Banking System - Design a database for a bank to provide personal banking services (e.g. saving, drawing, customer information etc.) 21. Amazon Database - Design a simple database for Amazon website to maintain their data (e.g. customer and transactions) 22. Facebook Database - Design a relational database for Facebook to store information e.g. user profile and short messages. 23. Hotel Database Management System - Maintain customer information, booking and hotel information (e.g. rooms, location) 24. Longleat Safari Park Booking System - May involve customer booking, parking, visiting sites and services. 25. Woburn Safari Park Management System - Provide manager with information to run the park, such as staff info, services, animal caring etc. 26. University Examination Management - To manage exams for the university (e.g. students, courses, time, room allocation etc.) 27. Gym Management System - To manage a gym involving customers, services, booking etc. information 28. Real Estate Management - A company manages property viewing and sales etc. 29. Airline Reservation System - To sell tickets and maintain customers booking information. 30. Premier League Football Scheduling system - A database to maintain the clubs match schedule (e.g. fixture, league table, club info etc.). 31. Rental Shop Management System - A shop to provide product rent services to customers. 32. Automated Electricity Bill System - To generate electricity bills for customers. 33. Citizen Management System - To maintain citizenship information for UK Boarder Agency, which may include visa issue, citizen information etc. 34. Mail Management System - A mail management system for post office (e.g. records on a daily basis). 35. Student Attendance Management System - Keep records of student's attendance of each module 36. Cinema Film Booking System – Make customers booking to watch films. 37. A database for Universe - To build a database to store general knowledge about universe for educational purpose. 38. Search Engine Database – To build a database to store information such as website links and keywords etc. for query 39. Questionnaire Database – Store questionnaires with multi-choice questions and answers 40. Student Timetable Database – To design a timetable for students based on modules, courses, year entries and semesters etc. information 41. Resit Management System - Involve students, time, rooms, modules, departments 42. A Basketball Club – Save match schedule, results and players etc. information 43. Holiday Planer - Provides clients with a full holiday planning service 44. Property Maintenance - Provides emergency repairing and plumbing etc. work. Sometimes uses invoices 45. Mobile Phone Sales Company – Provide customers with various brands based on different networks. 46. Stock Market Database – trade of stock involving buyers and sellers 47. Thesis Management System – A database with the information of thesis names, location and keywords etc. for quick information query 48. Car Sales – May involve car types and transactions 49. Bike Renting – Rents bikes to customers for a fee 50. Computer Repair Service – Repairs computers and computer accessories for customers 51. Oxford Museum – Maintains a list of collections and organizes exhibition events 52. London Underground Tracer – Maintains and traces customer travel records, such as underground lines taken etc. 53. London Tour Company – Provides tour service for customers 54. Oxford Fish Market – Records sales of seafood to customers; may include the amount of supply 55. Wolverhampton Smart Card System – Sales, recharge and usage of bus smart cards 56. University Document Archiving – Documents such as exam paper of students, thesis of students, important files from lecturer need to be archived and stored in some safe locations. Design a database to keep the records for quick locating. 57. Tesco sales – Records Tesco products and sales 58. Birmingham Playgroup – Provides child care services by staff. 59. Computer Childcare Voucher System – To record parents, children, and salary contributions to childcare etc. information, and transfer moneys to childcare service providers monthly 60. University Activity Contact List System – For an activity, record staff, students and other people involved information, which may include locations, job title or studies, schools, period at the university etc. 61. Local Swimming Club - Holding weekly meetings and an annual championship. 62. Piano Study Club – Book a course to study piano with a pianist allocated by the club. 63. Weather Recorder – To record and compare weather information from various sources (e.g. BBC) based on time and location. 64. Online Ride-sharing Service – Provides car share service to go to work for customers 65. 3D Printing Service – Provides service by printing various products for customers by 3D printer 66. Cleaning Service – Provides cleaning service to various companies. A booking is needed 67. A Software Development Company – Develops various small software for firms etc. 68. Party Organizer – Helps organize a party, including invitation of guest, activates during the party, and stuff purchase etc. 69. Wolverhampton Clinics – Provides GP services based on appointment and maintain customer and diagnosis records 70. Lunch Delivery Service – Provides lunch delivery based on order, location, time etc. 71. Model show – Organize model show including model invitation, ticket sales and location etc. 72. Stephen Hawking Secretary – Arrange Stephen Hawking's activities such as speech, lectures and travels etc. 73. World's fair – List of activities based on countries, and tickets sales based on activities. 74. Children Awarding System – Children doing housework or tasks planned, can get some reward such as time to watch TV and money to buy books etc. 75. One Sentence – The company does advertisement on various media by one sentence for customers. 76. FANGS DENTAL SURGERY – Classic dentist services. Specialising in routine and cosmetic work. 77. PORTIA SOLICITORS – property sales and purchases, commercial leases, wills and probate, personal injury claims etc. 78. A PLUS EXAMINATION BOARD – Provides examination papers to schools in many subject areas. Also keeps records of results and full candidate details. 79. RUBY – High quality diamond jewellery company serving national and international customers. 80. BBC -BRUMMIE BADMINTON CLUB, records member information, fixtures, results, etc. 81. TOPIARY TREE SURGEON –tree surgeon, landscaping and garden design company. 82. FASTJet AIRLINES – Airline Company offering flights to Europe and UK destinations only. 83. SWIFT TAXI SERVICES – provide local and national taxi services. 84. READALOT Library – implement a system for a library. 85. FRANCESCA'S FANCY DRESS SHOP – hires out fancy dress costumes, and accessories. 86. DR JONES LASER EYE CLINIC – Laser eye treatment clinic. 87. MURIELS BRIDAL WEAR – Sales and hire of wedding wear and accessories. 88. Jillys Fillys – offers riding lessons and sells range of equine goods 89. VIOLIN CLUB – A company of performers, travelling and performing to live audiences. also involved in recording work 90. ONLINE DATING - Individuals can find and contact each other over the system to arrange a date; a registration is needed; may incur a fee. 91. ONLINE PRE SCHOOL ADMISSION - Parents are required to register a primary school for their children before a deadline, both parents and children information is needed.   RESIT COURSEWORK Taking your topic, you are to supply a hard copy for the following deliverables:- DELIVERABLES: Section and Description Length/Diagram Maximum Mark a) ANALYSIS: analyse the business you are devising the database system for. Describe the business rules, and data involved. 1 page of A4 4 b) Using the Oracle Data Modeller Tool DRAW AN ERD DESIGN: AT LEAST 4 OR 5 entities ARE EXPECTED. Include cardinalities, attributes and relationship names. ER (Logical) diagram 9 Ensure the diagram is readable. c) List the relational headings 1 page of A4 1 Your tables must match your ERD, otherwise 0 marks. Select suitable primary keys for every table. a) Use ORACLE create your database (ensure you save your CREATE TABLE statement). Highlight any additional constraints added. Ensure AT LEAST ONE table has a COMPOSITE PRIMARY KEY. SQL script 3 The primary and foreign keys should be added at the same time as your CREATE TABLE statement, i.e., do not use ALTER TABLE to add them after. 0 marks if you use any other DBMS than Oracle b) Populate all the tables with data (having 8 or more rows per table). Show all rows, (use SELECT * from ;) of data in every table created. Good/suitable TEST DATA created. Note, the tables at the many end of a relationship should contain more data. 5 SQL & QUERIES: Devise 3 SQL queries that demonstrate appropriate use of the following: c) use of the BETWEEN logical operator; d) a DATE function; e) a SUBQUERY. At least one of the above queries should include data from more than one table. include the following for each query: - short sentence saying what the query is doing; - SQL query; - results 14 (4 marks each +2 marks for the results to be formatted appropriately) Marks will be deducted for weak or poor examples, or where any of the deliverables listed are omitted. Marks will be deducted for failing to show SQL commands execution. SQL commands must answer the question or test the functionality in the exact manner the question asks for. Use a COURIER font to present the results See the sample report on WOLF for examples on formatting. Pick one data manipulate command (DELETE, INSERT OR UPDATE) and: f) Devise a command that tests either a primary key or foreign key constraint. Explain any error message. Include the query and comment on any results. Show at least 2 queries: one that work successfully and one that does not. 4 SUBMIT A HARD COPY OF THE COMPLETED DOCUMENT TO THE MI STUDENT OFFICE. Deadline: Wednesday Resit week – 13th July 2016. SUMMARY OF THE MARKING CRITERIA: Max marks 40 Section Max. mark Comment a 4 Logical structure expected, in readiness to introduce entities. If weak – 1 mark b 9 Missing entities or several missing attributes = 0 marks c 1 Incorrect notation, poor table structure=0 marks d 3 DDL script with no errors and all primary and foreign keys = 2 mark Additional appropriate constraints = 1 mark. MUST INCLUDE AT LEAST ONE COMPOSITE PRIMARY KEY for full marks. Note, primary and foreign key definitions must be made within the CREATE TABLE statements. ALTER TABLE can be used to add additional constraints later, or to make minor adjustments to the data types. e 5 Poor data= 1 mark achieved f to h 14 For full marks one example of the following should be included in one of the three queries: • use of data in one query from multiple tables. Results should be formatted so they are readable i 4 Use of Data Manipulation commands. Show an example that works and one that does not. All error messages must be explained for full marks (2 marks max if there are no explanations). The report should be put together professionally, that is, include a contents page, each section correctly labelled. Ensure all the diagrams and results are presentable and in a readable format. If you cannot read it, neither can the lecturer marking your work! See the sample report on WOLF for further guidance.