Describe a simple database of your choice or design

Assignment Help Database Management System

Reference no: EM132317422

Database Design and Development Assignment -

For all the SQL queries in this assignment, need to submit their SQL statements constructed directly, that is, not to generate the SQL script for you by the GUI, as this would defeat the purpose of practicing how to formulate SQL queries directly.

Q1. Selected Exercises

i. Describe a simple database of your choice or design, along with the table/s representing the data, and illustrate the Insertion Anomaly through the real data or records there. Your database should be different from those already covered in the lectures or practicals.

ii. For database along with its inserted records prac8data.sql, do any 2 of the following database queries. For each query, take a screenshot for the query result and the screenshot is to include the SQL script window as well. Before finalising your answers, refresh your database by reloading prac8tables.sql and prac8data.sql, if necessary, so that the results are uniform across the board.

a. List P_DESCRIPT and P_PRICE for a product and the name V_NAME of the vendor who can supply the product. The output should be sorted according to P_DESCRIPT and then the V_NAME, all in the alphabetic order.

b. For the customer Phan Wang, list the names (i.e. PDESCRIPT) of all the products ever ordered by this customer. Do not repeat the product names in the result.

c. List all the customer order (ORDER_CODE), the fullname of the customer, and the corresponding total cost for each order.

Q2. Database modelling

In this part, you are asked to design a database to support an Accommodation Booking System for HappyHolidays (HH), which is a mid-size online reservation company. The major business requirements are summarised below in the Mini Case:

HH receives booking requests from prospective visitors for accommodation in various locations. Accommodation can be of different types; for example, house, apartment, motel room, cabin, etc. Each type is uniquely identified by a code (H = house, Ap = apartment). Each type could also have specific information such as rate, size (1 bed, 2, bed, 3 bed, etc.), location and minimum and maximum number of required stay (in days). Visitors details are recorded, at least their full name, telephone number, address, and postcode.

Additionally, each type can have various conditions or restrictions: for example, a visitor is allowed up to 2 pets if they rent a house, but they can't have any pets if they rent any other type of accommodation. Pet type, breed and name also need to be recorded.

If visitors take a pet, HH needs to be notified at the time of booking the accommodation, and the visitor is charged an additional fee for each pet. Pets are given a kennel each. Each pet is identified by name and collar ID. Also kennels are labelled to identify the pet in it at any time.

You are asked to develop a detailed Entity-Relationship model (ER) for this mini case. Your ER model should consist of a detailed ER diagram integrated with itemised discussions on the features of the entities and relationships and all the assumptions you made where applicable.

The ER diagram and the accompanying document should identify keys, constraints, entity types, relationship types, specialisation/generalisation if any, etc.

You must use the same notation scheme for the ER diagram as the textbook (use UML notations as shown in the last page of the textbook, and don't use Crow Foot notations), and the ER diagram should be strictly in the way shown or used in the textbook - using any other notations other than UML will result in loss of marks. ER can be drawn in draw.io, or even Microsoft PowerPoint. A screenshot of the ER can then be placed as an object in your assignment Word document.

i. The ER diagram should include, among others, representative attributes for all entity types, proper subclassing if any, and correct participation multiplicities for the relationship types. It should be meaningful and well designed, and should also include all relevant and necessary aspects, and indicate any supplementary business rules if you decide to introduce.

ii. Map the above ER diagram into a global relation diagram (GRD). The GRD should be in a form similar to Figure 17.9 (page 554, or 516 for edition 5) of the textbook, but all the attributes should be kept there too. Include in the diagram all the primary keys, foreign keys, and the multiplicity constraints.

Please note that an ERD is the artefact of the conceptual database design phase, while a GRD is the artefact of the logical database design phase which relates to the relational models. As such, a good ERD should be conceptually more concise and the relationships there should in general remain so rather than becoming extra entities as in a relational model.

Below is the given mini case for you to design your database for the ER and GRD as the instructions given above.

Q3. More analysis and SQL

i. Create the database tables in SQL (runnable on Microsoft SQL Server) for all the relations in your relation diagram, and enforce there all the relevant constraints including primary keys and foreign keys. It is expected to have at least 3-6 attributes in each table.

Adding records: Fill the tables with sufficient realistic data - generally around 3 tuples or more per table. For example you may assume a visitor made at least 3 bookings on different dates. Your data should be sufficient to illustrate meaningfully the working of the general queries to be completed below:

ii. In the most appropriate table (say visitor) add a tuple with meaningful data about your own details (your student name and ID + ... etc.): do the same for your group member's details in another tuple if you have a group member.

List the content of your tables with screenshots. Screenshots of active windows (under Microsoft Windows) can be obtained by pressing CTRL-ALT-PRTSC keys together (or you may use the snipping tool). Your screenshots must contain your username as in the below example, and you may list several tables on a single screenshot if you wish.

iii. Write in SQL the commands to complete the following queries, and show your results in screenshots. Where parameters for the queries below are not completely specified, the parameters should be chosen so as to generate non-trivial (non-empty) results for the queries (meaning you must update the tables above to ensure you can answer the questions).

(a) For a given visitor and a given date, list all the names of those who have made a booking within a date (ie: from 6/11/2018 to 20/11/2018), along with the type of accommodation they booked and the location of the accommodation.

(b) Show the number of times a visitor had made a booking. List the visitor's full name and contact along with the booking date.

(d) For a given type of accommodation, say 3 bedroom house, show what is still available (have not been booked) and the location/address of these still available.

Attachment:- Assignment Files.rar

Reference no: EM132317422

Questions Cloud

Calculate ilex corporations tax liability for 2018 : Corporation has ordinary income of $260,000, a short-term capital loss of $60,000, and a long-term capital gain of $20,000.

What are the options available to gerry and mary : What are the capital gains tax considerations on splitting the fund into two SMSFs - What are the options available to Gerry regarding the trustee structure

Prepare the spreadsheet entries to record these transaction : In Journal entry form, prepare the spreadsheet entries to record these transactions for Collins Company's 2016 and 2017 statement of cash flows.

Identify reason to necessitate change in criminal justice : Identify a reason to necessitate change in the criminal justice organization of your choice and discuss the process in which the change will occur.

Describe a simple database of your choice or design : Describe a simple database of your choice or design, along with the table/s representing the data, and illustrate the Insertion Anomaly through the real data

Explain the purposes of the uniform crime reports : Explain purposes of Uniform Crime Reports and National Crime Information Center. Identify various weaknesses related to accuracy of Uniform Crime Report.

Develop transport and logistics business plan-achieve budget : Develop a transport and logistics business plan and achieve budgets. Explain how you would ensure relevant workplace processes are documented.

Prepare the journal entry to record the impairment of asset : It is expected that the cost of disposal will be $20,600. As of December 31, 2017, the equipment has a remaining useful life of 4 years.

Discuss how you would interview a suspect : Based on authoritative sources (including peer-reviewed articles from the library, Fraud Examiners Manual, Big 4 CPA firms, ACFE (e.g., Report to the Nations).

Reviews

len2317422

6/6/2019 2:41:46 AM

My assignment is on database design and development, using SQL. From question 1 I just want to get the first part done and all of question 2 and 3. All the instructions aree on thee assingment pagee. From question 1 I just want you to complete part 1. From question 2 and 3 all of it at the end of the page there are additional exercises. Assignment question 1 asks to complete additional exercise 1 from practical 8(I have attached it)). p8 pdf I sent has the question which the assignment question 1 part 1 talks about if u look at the assignment question 1 it asks to complete additional exercise 1 from prac 8. Also please format the assignment following the instructions at the end of assignment.

len2317422

6/6/2019 2:41:39 AM

For all the SQL queries in this assignment, students need to submit their SQL statements constructed directly, that is, not to generate the SQL script for you by the GUI, as this would defeat the purpose of practicing how to formulate SQL queries directly. Students' main document submitted for their answers to this Assignment must be written in Microsoft Word, not in PDF.

len2317422

6/6/2019 2:41:33 AM

This assignment needs to be completed in a group of no more than 2 student members. If such a group is formed, both members must come from the same campus, and also within the tutorial classes of the same tutor whenever possible. While lecturers and tutors will help as much as they can, it is essentially each student's own motivation and responsibility to form a group for this assignment. By default, each student is working in a group containing a single member of himself. Students are expected to continue with their existing assignment group or form a new group if they haven't formed a group for Assignment 1. If any student is making a new assignment group, thus leaving a previous assignment group, he must first obtain a written approval from his tutor or the unit convenor, unless he will not make use of any work jointly done in the previous team work.

len2317422

6/6/2019 2:41:26 AM

Group Issues and contingency plans - In the rare case of one group member becoming seriously ill or uncontactable or not responding, the other member should consider forming a different group (if still have minimum 2 weeks to work together) or otherwise working on his or her own for the assignment. As in real life, everyone should have a contingency plan, or Plan B, and failure of assignment partnership at any point of time will not be accepted as a legitimate reason for an assignment extension. However, a student is obligated to properly notify his or her existing assignment partner in good time (via email) should it become imperative that the student need to terminate the existing partnership due to unexpected circumstances. A copy of any such emails from assignment partner can be attached as an image to the end of the assignment as evidence.

len2317422

6/6/2019 2:41:19 AM

A single plain-text file containing SQL statements for creating all the tables and making all the queries. The script should be executable on the School's Microsoft SQL Server, otherwise the corresponding marks in the above listed items will be deducted accordingly. Marks will be deducted in the corresponding questions if this SQL script in plain-text file is not submitted. Each student must state explicitly who he or she once teamed up with if that person is currently no longer the group member for the submission, unless no shared work is involved. Students are not permitted to have their shared work for this assignment with more than one person (the team member) including potential former team member, unless approved by the unit coordinator in writing.

len2317422

6/6/2019 2:41:10 AM

Note on Submission - This assignment must be submitted electronically via vUWS before the due date. No email submissions will be accepted. It is the students' responsibility to retrieve and keep all their submission receipts. If in doubt, consult your tutors well before the submission due date. Your submission must include the following parts: main document in Microsoft Word that describes your solutions question by question (make sure to label each question), in the right order, including pertinent diagrams, screenshots, and SQL scripts whenever relevant. Add a footer with your students name and ID, in addition to the title page that includes these details. And A plain text file as an Appendix at the end of the document, containing all SQL scripts in the order of their appearance in the main document for each question.

len2317422

6/6/2019 2:41:01 AM

The electronic submission should contain the paper work in Microsoft Word, and the pertinent SQL source code. Otherwise 1 mark may be deducted for the missing SQL source parts even if the code is already contained in the main Word document. Please note that if your SQL source code gets rejected by the SQL Server at the School, you automatically lose 50% of the marks allocated to that coding part. Each group must submit exactly one copy of their assignment solution electronically by one of the team members. If the other group member really wants to submit it as well due to whatever reasons, then the name of the submitted files must start with "please_ignore_" (such files will not be treated as regular submissions and will be ignored during the marking). Otherwise 1 mark may be deducted for the duplicated electronic submission.

Write a Review

Required(*) Message

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

User Account

All Pages