CIS2002 Database Design and Implementation | My Assignment Tutor

_________________________________________________________1 Database Design and Implementation Semester 3, 2020Assignment 1 Due Date:11:55am AEST (23:55 UTC/GMT) Friday 09 January 2021Weighting:40%Total Marks:100 Please submit this assignment using the assignment submission Portal on thecourse web page.Submit a PDF file ONLY. Do not submit a wordprocessor file (e.g. docxformat). If you use Word, you can export as PDF directly from Word 2007 orlater and OpenOffice, but with Word 2003 you need to use an extra applicationlike PDFCreator1 to produce a PDF version.IMPORTANT INFORMATIONSubmit your assignment electronically by the due date. Use the Assignment 1 submissionlink on the study desk.Your assignment is expected to conform to official USQ data modelling and normalizationmethodology. This methodology is based on Clive Finkelstein’s techniques (SelectedReading 2.1 and 2.2) and all examples in the lectures, study book and the tutorials use thismethodology.Modelling answers which do not use the required USQ methodology, will most likely beawarded a mark of zero.It is perfectly acceptable if you submit neat hand-written ERDs. Alternatively, you mightwish to use Word. If you use CASE or drawing tool, you must adapt the drawing toconform to USQ methodology._________________________________________________________1 A (SQL) (60 marks)For each question, three marks will be awarded for the SQL and one mark for the correctoutput.The following E-R diagram represents the JustLee Books database. The script for the tablecreation is located on the Moodle website for the CIS2002 course under the assignmentspecifications. Please ensure that you re-run the script to reset the tables to their defaultstate before starting your assignment._________________________________________________________1 this question, you will use the JustLee books database. The JustLee database includingappropriate data will be made available on the USQ Oracle server. You must use this data.Write SQL queries to solve the following specifications. Include the query AND THEOUTPUT. A screen dump of the output is acceptable. Show as many rows as you can. Ascreen dump is usually done via the ALT + PRNT SCRN command sequence or use the‘Snipping Tool’ under windows to capture parts of the screen.While the output helps to understand your solution, you should not be analysing the outputof the query in detail. As long as you are confident that your query corresponds to thequestion completely, your output may not be significant. You can have an output that saysNO ROWS FOUND and it could be a perfectly valid output as long as your query fulfilsthe requirement. Explain why the query may not contain any output in this instance.Questions1. Write an SQL query that displays the ISBN, book title, publication date andcategory. Order the list by category.2. Amon is running the logistics operations of JustLee Books bookstore which assignsdispatch resources for delivering orders to different states across the country. Hisdispatch operations is based on region codes. Write an SQL query that displays theorder number, the book title (rename this to DESCRIPTION), quantity, order date,zip code and region.3. JustLee Books bookstore would like to reward their referring customers with giftvouchers for any new purchases from first time patrons. Write an SQL query todisplay the full name of all the referee customers in a single column (byconcatenating first name followed by a comma then a space then last name), renamethe field ‘Customers’, their address, zip code and email.4. List the customer number, first name, last name, order number and order date for allorders which have not been shipped. Order the resulting set by the order date inascending order.5. Write a SQL query that displays customer number, last name, first name, and cityfor all customers who reside in the south eastern states and who have not beenreferred.6. The sales and marketing department of the JustLee Books bookstore would like tounderstand the buying patterns of their books by some of their customers. Write anSQL script to display the customers’ full names, book title, quantity purchased anddate purchased. Which book(s) appears to be most popular among customers?_________________________________________________________1 The sales and marketing department of the JustLee Books bookstore would like tounderstand the buying preferences of their books by some of their customers; so thatthey can recommend similar genres of emerging book titles appropriately. Write anSQL script to display the customers’ full names, ISBN, Book title and category ofbooks purchased. List the result by customer’s names so that it becomes easy totrack the preference of these individuals.8. A new batch of book releases have just been purchased by JustLee Books bookstore.The advertisers are eager to get down to pushing sales of these books as soon aspossible through email blasting. These books come from a variety of genres that fitinto the stock list of what the JustLee Books bookstore is currently carrying (i.e.more of the same). However, their customers may not know that these “new” booksare currently available at JustLee Books bookstore. Help them write an SQL scriptto display the customer’s full names, the book categories from which they haverecently purchased and their emails in a table. Order your table by book categoriesso referencing becomes easier. If customers do not have an email, advertisementflyers will be sent to their postal addresses.9. The sales team would like to know if they are able to meet their profit margin quotafor book sales in their most recent months of March and April. Display publishername, book title and retail price for only discounted books, after subtracting theiravailable discounts. Name the new field ‘SELLING PRICE’ and order by publishername ascending and discounted price of the book in descending order.10. It has been decided that old books in stock which are selling below acceptable profitmargins will have to get replaced by new releases asap, so that JustLee Booksbookstore is kept up to date with both reading material and running costs. ListISBN, title, retail costs, category and published date of all the books that are sellingfor lesser than 40 dollars and where (cost of the books is more than 20 dollars andwhere the published date is before the end of the financial year for 2005/2006 – i.e.30 June 2005)._________________________________________________________1 B (Data Modelling) (40 marks)In this section, you will construct data models per the given specifications. For each of thefollowing questions, you will need to provide an ERD and a list of relations (entity list).1. An ER diagram: Show all entities, relationships, cardinalities and optionalities.Also, include all intersection entities but do not create entities that are notspecifically covered by the specification. You must use the Finkelstein methodologyas per the study book and tutorials.2. A list of relations (entity list). Produce complete relations for all entities andattributes. Show all primary and foreign keys. Include all attributes that arespecifically mentioned and all key attributes. You may need to create primary andforeign keys that are not specifically mentioned but do not create any otheradditional attributes.Question 1 (10)An organization is made up of multiple departments, each department having a name,identifying number, and an employee who is a manager. A department may be located indifferent places. Information about employee includes name, identification number, birthdate, address, sex and salary. Each employee is assigned one department. Employees maybe directly supervised by another employee. Each project within the organization iscontrolled by a department.Employees (not necessarily from the controlling department) are assigned to projects.Information about projects include project name, number and location.Question 2 (10)A hospital contains a number of wards. Information about wards include name, totalnumber of admitted patients, date and time. Patients may be admitted to hospitals based onexternal doctor recommendations, emergency or by medical consultants of the hospital.Patient details include name, age, phone number, address and a medical ID. Each patient isassigned to one acting specialist in charge from the hospital’s doctors and may be assignedtests during the length of their admission based on their medical condition. Each medicaltest records the time, date, unique test ID, category and final results of the test. The testsmay be examined by their specialists in charge or by other doctors if required.Question 3 (10)The University Housing Office receives many applications from students requesting anapartment on campus. The housing villages are sited in five different locations and eachvillage has about 500 apartments. Each apartment falls into one of the different apartment_________________________________________________________1 of a single unit, a double unit or a studio unit and offer tenants with either afurnished or an unfurnished accommodation. The housing office keeps information aboutthe names, telephone number, marital status, occupants and affiliated college anddepartment of the current residents which they are enrolled in. Rent collected every week iskept by the rentals office and tracked by the agreements residents have signed at the time ofmoving into their accommodation. Residents have the option of paying for only rent or acombination of rent and either one or all of the three options: water, electricity and internet.A receipt will be emailed to residents for weekly rent which has been paid up to date.Reminders will be emailed to residents for late or overdue rent arrears.Question 4 (10)A truck driver may be assigned to many teams over time. A team comprises of a minimumof one driver but could have up to four. For driver, we store the name, license number andbirth date. For team, we store the team identifier and base location. We also need to storethe date a driver is assigned to a team, the date a driver leaves the team and the totalkilometers the driver has driven within the team.MARKING CRITERIASECTION A (60 marks, 10 questions 6 marks each)1. Three (3) marks awarded for each correct SQL statement and one (3) mark for thecorrect output.2. Alternative approaches to the model answer will often be accepted unless they donot follow the rules / requirements set out in the specification, are poorly optimisedor are poorly constructed (SQL) or utilise features of SQL not covered at this stagein the course.3. Part marks may be awarded if an answer only has a small problem or an alternativesolution is presented that works but is not ideally optimised / constructed.SECTION B(40 marks, 4 questions 10 marks each)1. Entities – no missing entities, appropriate names, no redundant entities, etc. (3)2. Cardinalities and optionalities all shown and correct. (2)3. Complete list of relations, showing all applicable attributes, primary keys andforeign keys. (4)4. Sophistication: well-presented solution; good layout; innovative approach; correctdiagrams/notation; solution easy to read and understand; solution comprehensive.(1)


Leave a Reply

Your email address will not be published. Required fields are marked *