Database Management for Business Report
Added on 2019-09-23
12 Pages4012 Words284 Views
|
|
|
ISY1002 (ISY103) Database Management for Business2018.3 Group Assignment – Database Report & PresentationTotal Marks: 50Assessment Weighting: 25%Aim: To give you practical experience in with database modelling, normalization and writing SQLstatements.Assignment Summary: The project specification details provided in this document and youmust make use of the following specifications to come up with your solution. Make appropriateassumptions where ever required.Please make sure that your group must meet your lecturer/tutor at least twice to update theprogress of your assignment. Background Information for Sample Database ProjectSydney Dance Academy (SDA) requires a database system to manage their newRegistrations, enrolments, timetabling, room allocations and payments.Currently SDA has 8 (Sydney CBD, Parramatta, Chatswood, Hornsby, Liverpool, Blacktown,Botany, Castle hill) locations across Sydney. Each location maintains the required data on flatfiles. Each location has a Branch manager that manages all the operational data required tomanage the branch. Apart from branch manager all the other staff that SDA has are Danceinstructors. They are a helping hand at the reception as well.When a prospective student rings up or comes into the reception at one of the Dance academylocations to enrol into dance classes for the first time they are asked to provide their name,address, and telephone number. The student may enrol into one or many dance classes. SDA teaches Dances of various styles (Example Jazz, Belle, and Hip Hop etc) and each dancestyle can have different rates per class.SDA enrols students to dance classes for the whole term and there are SIX (6) terms per year.Each style of dance may have classes for various age groups.SDA has the following age groups – Age group 1 – CHR (children): 5yrs – 8yrs, Age group 2 –PRETEENS (Pre – teens) : 8yrs – 13yrs and TEENS/ADULTS (Teenagers and Adults): 13yrs+.Each Dance class has a specific style it belongs to, specific age group, a set timetable per term(day of the week and start and end time), a location (One of the 8 locations), room numberallocated and each Dance class is capped to 15 students maximum. Each Dance class has an instructor assigned. There can be multiple Dance classes timetabledfor one type of Dance in a week. An Instructor may be timetabled for more than one danceclasses in a week however a single Dance class can only have one instructor. For enrolling in each dance term a student must pre-pay all the classes on the term. Thepayment can be either made by a bank transfer or they can pay electronically or cash byapproaching the Dance class location in person.Your group has been approached by SDA to create a Database that centralizes all theAcademy data so that they can manage their enrolments, timetabling, room allocations andpayments accurately. They want a software/online application in future to be built on thedatabase you design so that the operation of the Dance Academy is greatly improved.1 | P a g e
SystemRequirementsThesystemisaprototypesystemandassuchisnotafullproductionversion.Youwillberequiredtoenterarepresentativesampledataintoyourtablesinordertotestthedesignandoperationofyourdatabase.Project Specification1.Part A (20 marks)1.Your first task is to study the Case study requirements and gather as muchknowledge to list all the main Entities and attributes.2.Create an Entity Relationship Diagram (ERD) to help you decide on the relationships.(10 marks)Your entity relation diagram that models your database design should:i.Include all entities, relationships (including names) and attributes.ii.Identify primary and foreign keys.iii.Include cardinality/ multiplicity and show using crow’s feet or UML notation.iv.Include participation (optional / mandatory) symbols if applicable.The E-R should be created as part of a Microsoft Word document. Hand-drawndiagrams will not be accepted. It is recommended that you complete your ERDusing Draw.io, Visio or try www.gliffy.com (Search for ERDgliffy to get started.).3.Create relationships between tables and enforce the referential integrity as shown below.Relationships:Student can enrol themselves into more than one Dance Class. One type of Dance can have more than one classes timetabled and they can run at various locationsThe dance styles can be Jazz, Belle, Hip-hop, Salsa etc.Each room can be timetabled more than once as long as the classes don’t overlap.Each enrolment needs a payment that needs to be pre-paid, but a student is allowed to make part payments. Means they can pay the whole amount using different payment methods like cash, debit card, credit card etc.4.The database should include suitable validation and integrity checks as well asappropriate referential integrity checks. That is, AS A MINIMUM, your systemshould ensure that the following events cannot occur:Referential Integrity Constraints:A class cannot have a student who is not registered means does notexist in the system beforehand.A student cannot be deleted from the system if a student is enrolled in acurrent dance class. Similarly an instructor cannot be deleted from thesystem if they are instructing a current dance class.2 | P a g e
An enrolment cannot be deleted if a payment has been made and danceclass is allocated to the student. SDA does not cater any refunds.Only Dance styles entered in the database can be attached to a danceclass. A new form of dance style for a dance class may be only added tothe system after the required dance style is added first.Rooms cannot be deleted if there are dance classes already allocated tothat room.Payment methods cannot be deleted once payment methods have beenrecorded against payments.2. PART B (15 Marks)1.Using MySQL, you are required to develop a demonstration prototype system that handles new Registrations, enrolments, timetabling, room allocations and payments. Use MySQL to create a new database called SDADB. Create tables according to your ERD. Follow a standard naming convention for table names and also field names. Avoid using spaces and any special characters in table and also field names. Use underscore_case or use camelCase to separate parts of a name. (10 marks)2.Add at least two new records into the appropriate tables to include students details, instructor details, location details, dance styles, dance class timetable, rooms details etc. (5 Marks)2.Part C (24 marks)Use the Sydney Dance Academy (SDADB) database that you created in MySQL to design and execute SQL queries that answer the following questions. Number your answers to each question clearly. The answer to each question must be tabulated as shown in the example below and include the SQL statement and also the output that is produced when you execute the statement in your database. The output includes the records that are listed and also the message that appears when you run the SQL statement. For example:Question 1:SQL:SELECT lName, position FROM Staff WHERE salary > 20000;Output:+-------+----------+| lName | position |+-------+----------+| Brand | Manager || White | Manager |+-------+----------+2 rows in set (0.03 sec) Do not use screen captures to display the SQL statement or the output. 3 | P a g e
You should right-click on the MySQL Command Prompt window; choose Mark and then press the [Enter] key to Copy and then Paste into your Word document that includes the answers to all questions. Format and indent the clauses in your SQL statements for better readability and understanding as shown in the example above. Statements must be syntactically and semantically correct. Format both the SQL and also the Output in Courier New 10 or 11 point.Each question is 2 marks.1.List the first name, last name of students (join student’s first and last name with a spacein between and use the alias Student Name for the column heading), and email address. Sort the output in ascending order by the Student Lastname.2.List all the dance styles that has got a rate higher than $12 per class.3.Retrieve Students’ last name, first name, mobile, and email for all students who do nothave a mobile phone number recorded in the Student table. Sort the output inascending order by the Student last name, and then first name.4.List all Student last name for all students that have a suburb that has the word ‘hill’anywhere in the suburb name. Sort the output ascending order by the last name ofstudent.5.List all the instructors who are currently timetable for 5 or more dance classes.6.For each student who has been enrolled to dance classes multiple times display thetotal amount of money they paid.7.Count the number of students enrolled in each type of Dance style.8.Display the current timetable of the classes done in Room 1 and 3 at Chatswoodlocation.9.Display all the students’ information who are currently not enrolled in any of the danceclasses.10.List the payment method description and sum of payment amounts for all payments which were made after 1-July-2017.3.Part C – Every group member needs to write this part individually and add to the documentation (4 marks)1.Write a page to the department manager as a personal reflection that describes your experience building the database. You can discuss any challenges / difficulties that youexperienced or solutions that you found. Comment on any limitations and / or strengthsof your database design. Comment on whether your database meets all the system requirements as specified in Part A Question 4. Avoid making excuses or comments that reflect negativity. Include an acknowledgement of all students you have spoken to about the assignment.4 | P a g e
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents