1. Create an MS Word document named “Your Given Name – Your Student Number – Party Kids.docx" e.g. Harpreet – 123456 – Party Kids.docx. 2. Create an ER Diagram from the above scenario, being sure to:
a) Use Crow’s Foot Notation b) Mark cardinality, existence, primary keys (with an underline), and foreign keys (with the letters “FK” in brackets).
c) Ensure the entities are in a normalised state d) Write any assumptions you make if you think it important to clarify the reasons for building particular relationships, creating particular attributes, or leaving something out.
e) Place your name and student number within the drawing f) Insert that ER Diagram into your Word document. If you use Lucidchart or similar you could take a screenshot. ITDA1001 – Database Fundamentals Copyright © 2018 VIT, All Rights Reserved. VIT and its logo are trademarks of Victorian Institute of Technology 3 Task 2 (45 Marks) Create your solutions for following tasks in Microsoft SQL Server and:
• Write a T-SQL comment above each SQL statement you write, stating your name, the task you are solving, and the purpose of the code. For example, “Harpreet. Q1. Creating a database” and “Harpreet Q1: Creating a table”, above the statements required for question 1. • Paste all the SQL code that you write into your MS Word document. That is, paste it as plain text • Paste screenshots from Microsoft SQL Server into your MS Word document. The screenshots should show your code along with the relevant messages window and/or outputs window .
Note: o For screenshots, keep the images small by using tools like Windows-Shift-S or the MS Word Screenshot tool. Don’t clip the entire screen or a whole window. o Note that you could gather up all of your solutions for Tasks 2A and Task 2b into 2 batches and run each batch in one hit. o Where necessary, attach labels to the screenshots in your MS Word document so as to make it clear which screenshot or portion of a screenshot belongs to which solution. Task 2a (35 Marks) 1. Write two CREATE statements. One to build a database named “PartyKids” and one to build within it a table named “Customer” which will store details about PartyKids customers.
The columns should match the attributes listed in your ER Diagram. All columns should be required columns (that is, don’t allow NULL values). 2. Use a single INSERT statement to create the details of 7 customers. Name one of the customers “John P Smith” and record his address as 12/1 Flinders St, Melbourne 3000. [Note: If you didn’t include address details in the Customer entity in your ER Diagram, do so now and then redo task 2a.1] 3. Use a SELECT statement to display full details of all customers 4. Change the address of John Smith to 15/1 Flinders Street and then display the name and address only of all customers 5. Display the details of all customers whose first name that starts with the letter “J” 6. Display the details of all customers who live in Victoria. Note that all Victorian postcodes are in the 3000’s and no other states use the 3000’s.
ITDA1001 – Database Fundamentals Copyright 2018 VIT, All Rights Reserved. VIT and its logo are trademarks of Victorian Institute of Technology 4 7. Delete John P Smith from the database 8. Add a 2nd table to your database. o It should store details about bookings made by a customer so name it “Booking” or something similar. If your ER diagram doesn’t show a customer entity linked to table that lists bookings (party hires) made by customers, then add one now.
Ensure your Booking table contains, at the minimum, attributes to store the date of the booking and any customer feedback. o Build a relationship between the Booking table and the Customer table and make it impossible to create a new Booking that doesn’t belong to an existing Customer. o Insert the details of at least 6 bookings. Enter details into every column of each record. The bookings should all belong to just 3 customers – 1 for one customer, 2 for another, and 3 for the 3rd . 9. Use a DELETE statement to try to delete a customer who has a booking. It should not be possible. 10. Display all bookings grouped by customer. Display full booking details, but don’t show any customer details. 11.
Display the customer names and booking dates of all customers who have made a booking and sort them from the most recent booking to the least recent. 12. Display the names of customers who have not yet made a booking. Task 2b (10 Marks) 13. Create all the tables you have drawn up in your ER Diagram and build the relationships between them. Choose your data types carefully. Note that you will need to buid the tables on the “one” side before the tables on the “many” side. 14. Add at least 3 records to every table and then display them all. Note that you will need to insert records into the tables on the “one” side before inserting into tables on the “many” side. 15. Display the names of all customers who have rented inflatables 16. Display the names of all customers who have rented chairs or inflatables 17. Display a count of the number of booking days of each customer 18. Display the total amount of money received from each customer. That is, display one one value per customer. ITDA1001 – Database Fundamentals Copyright © 2018 VIT, All Rights Reserved.
VIT and its logo are trademarks of Victorian Institute of Technology 5 Task 3 (20 marks) 19. Ms Pop is curious to know why a computer expert like you would draw a diagram before using database software. Write 200 to 250 words explaining the purpose of creating your ER Diagram before building the database 20. Ms Pop is thinking about accepting credit card payments from customers and keeping a record of the payments in her database. She is concerned about storing personal details such as credit card numbers and customer addresses. Write 150 to 200 words naming the main legal issue at play and the techniques you could use to help secure her database. Submission Instructions The submission date is specified on your Moodle page at the Assignment dropbox. Format your MS Word document neatly and professionally before uploading it. Do not submit Microsoft SQL Server files or links to any other files. Only one MS Word document per student will be opened and all other files will be ignored.
Answer:
- Justification for starting with a diagram instead of the database software.
The process of coming up with diagrams which are referred to as entity relationship diagrams is called entity relationship modelling and a stage in database design that is done prior to the actual database implementation is a database management software (DBMS). According to good database design standards the first step to designing a database is to model the database using either top-down approach or bottom-up approach. Use of top-down approach is the approach that designs the database using entity relationship diagrams while bottom-up approach uses normalization to get the entities from the business rules. These two approaches are used interchangeably to help the database designer to understand the database comprehensively thus making sure that all entities, their attributes and the relationships between the entities are captured. Modelling the database before doing the actual implementation makes the implementation process easy since everything about the database is already stated thus the developer only has to make the tables based on the entities and the relationships achieved during the modelling process. This helps save time and avoid errors which could lender the database ineffective because of errors that could have been captured during the design stage of the database development.
- Legal issues resulting from saving of credit card details and how they could be solved
With the current and expected growth of the partykids business, the business will have to start recording details of credit cards for customers who pay for their booking using credit cards. This will help the business by making the payment process easy as all the information about a customer is already in the database thus more orders will be processed and at a higher rate. Storing customer’s credit card details can bring legal issues to the business because is the information is accessed by malicious users like hackers, the information could be used to defraud the customers and this would be blamed on the business as it is supposed to make sure the customer’s information is safe from all risks. There have been many cases where companies have gone broke because their private information has been accessed by hackers.
To prevent access of this confidential information, partykids will have to make sure that the database is deployed in a very secure environment that is not easy for hackers or malicious users to access. Apart from the secure deployment environment, the business should make sure that the information is stored in an encrypted format that is not easy to decrypt if the malicious users are able to bypass the secure environment.
Talk to our expert to get the help with Itda1001 The Stage In Database Assessment Answers to complete your assessment on time and boost your grades now
