辅导CITS1402课程程序、写作SQL课程设计程序、SQL编程

” 辅导CITS1402课程程序、写作SQL课程设计程序、SQL编程DRAFTCITS1402 ProjectGordon Royle2020 Semester TwoSo far in this unit, the labs have been focussed on writing SQL queries learning how the SQL rowprocessing-machinecan be used to select, manipulate and summarise data contained in multiple relationaltables.This project, really a mini-project, is going to focus on some of the other aspects of databases.A database designer builds the database schema and possibly enters the initial data, but over time thedata evolves as rows are inserted, updated and deleted during the day-to-day use of the database. Animportant role of the Database designer is to make the database resistant to data corruption caused bycareless users.This project explores some of the steps that a database designer can take to enhance the long-termintegrity of the database.The questions may require you to undertake your own research into how certain SQLite features areimplemented. The official documentation is located at httpss://www.sqlite.org/docs.html/index.html, and there are numerous SQLite tutorial sites with examples.Project RulesFor the duration of the project, different (stricter) rules apply for obtaining help from the facilitatorsand help1402 for the duration of the project.1. Absolutely no pre-marking requestsDo not show your code to a facilitator and say Is this right?Firstly, this is not Fair to the facilitator, who is there to provide general assistance about SQLand not to judge whether code meets the specifications.Secondly, from previous experience, such requests often degenerate into the situation wherethe facilitator helps out with the first line of code, then the student returns five minuteslater and asks for help with the second line of code, and so on, until the final query is mostlywritten line-by-line by the facilitator and not the student.Facilitators are there to gently nudge you in the right direction, not by just giving the answerand supplying code that works, but by making general suggestions on SQL features, remindersabout what concepts might be useful, and advice on how you might investigate and resolveproblems yourself.2. No validation requests for your submissionPlease do not ask the facilitators anything about the mechanics of making a valid submissionsuch as file names, due dates etc. This is not their job and it leads to awkward situations wherea student submits something that is obviously incorrect, but then claims that the facilitatorsaid it was ok. 辅导CITS1402课程作业、写作SQL课程设计作业DRAFTYou are responsibleFor writing, testing, formatting and submitting your code correctly, and ifyou have any doubts about what is required, then please ask on help1402.3. Avoid low-quality help1402 postsBefore the mid-semester test, there was a huge spike of activity on help1402. While I encouragethoughtful Questions and try to answer them promptly, the sheer volume of questions almostoverwhelmed me.Even without help1402, this unit is already consuming far more time than I am meant tospend on it, so I have to cut back. A lot of my time earlier in semester was spent dealing withrepeated or low-quality questions, so Id like to eliminate (or at least reduce) these. Ideallyhelp1402 should be a lower-volume but higher-quality forum.So before you post, please ensure that: Your question is actually newDont ask a question that has already been answered in another thread. You can eithermonitor help1402 daily so you always know what has been discussed, or use the searchfacility. You actually need external helpQuite a few posts have asked for confirmation that the output of a SQL query is correct,even though it would be straightforward for the user to check this themselves.Given access to an actual database, you should normally be able to tell how many rows ofoutput there should be by using SQLiteStudio to examine the data directly or manuallyrunning a few simpler queries.So just make sure that you have made reasonable efforts to test your query yourself beforeposting to help1402 Your question is precisePlease dont post Vague or overly general requests for assistance such as: I tried usingrandom SQL but it didnt work. Any help.All coding starts by forming a logical plan for extracting the required information fromthe database. Of course you have to keep the general overall structure of an SQL queryin mind in terms of the sorts of things that SQL can and cannot do, but try to get a clearidea of what you want to do before you start actually coding it.While forming the plan, you may notice that you need a table or a value that is notactually stored in the existing tables, but needs to be computed. This is when you thinkabout how you can use subqueries to create the table or compute the value.When it is time to implement your plan in SQL, remember that very few people canjust sit down and code an entire complicated SQL query from first line to last line, partlybecause the order in Which the keywords occur is not the order in which the actual steps ofthe row-processing are conducted. So write and test small portions of the code separatelyand then put them together. For example, if counting parking tickets for black and whitecars has to be done for every state, then first write the query for just one state and oneof black options, and then gradually extend it.Finally, remember that you are in control you are the coder and the machine is doingexactly what you tell it to do. If you accidentally tell it to do the wrong thing, then workout why it is doing the wrong thing (by mentally going through the process) and changeit.While coding certainly requires experimentation and testing, it should be a systematicprocess. In other words, just randomly changing one SQL keyword to another or shufflingaround the lines of code is not an effective method of coding. Your question includes no (or minimal) actual codeAs usual, dont post actual code to help1402, instead giving just a verbal description orposting a redacted screenshot (i.e., with key parts blurred or otherwise obscured). 2DRAFT(Actually, almost Everyone is already doing the right thing with obscuring their postedcode, so this is just a reminder to keep doing it properly rather than a change in policy.)Dodgey Brothers Auto RentalsWayne and Arthur Dodgey run a car rental business called Dodgey Brothers Auto Rentals andwant a database to keep a record of their cars, customers and rentals.They have implemented a SQLite database themselves that is adequate, but after a few months use theyhave noticed some problems. Some data is clearly incorrect, while the data in some tables is inconsistentwith the data in others.You are given the schema of the current database and discuss the requirements with Wayne and Arthur.The database has four tables, namely Car, Vehicle, rental and Customer which have the followingstructure:The table Car has data for types of carThe table stores data about types of car (not individual vehicles).CREATE TABLE Car (carMake TEXT,carModel TEXT,carYear INTEGER,dailyCost INTEGER,kmCost REAL)A typical row in this Table would be something like:(Hyundai,i30,2020,30,0.10)The first three fields describe a type of car, in this case a 2020 Hyundai i30, and the last two fieldsindicate that Dodgey Brothers Auto Rentals rents a car of this type for$30 per day plus$0.10per km.Wayne and Arthur indicate that the combination of make, model and year uniquely determines a cartype, and that the daily and per-km costs depend only on this car type.If a customer rents a 2020 Hyundai i30 for 3 days and drives 200km, then the cost of this rental will be 3 30 + 200 0.10 = 110.The table Vehicle has data for actual vehiclesThis table stores data about the individual vehicles in the Dodgey Brothers Auto Rentals fleet.A typical row in this table would be something like(Hyundai,i30,2020,WDCGG5GB8AF429863, 15199)The fields carMake, carMode and carYear have the same meaning as in Car while VIN is the cars VehicleIdentification Number which is a unique code stamped onto a metal plate and riveted to the cars frameby its manufacturer. The code is a 17-digit string containing letters and numbers in a format similar tothe example above. Dodgey Brothers Auto Rentals may have several cars of the same type, butit is impossible for two different vehicles to have the same VIN.The odometer field lists the number of kilometres on this vehicles odometer, so this particular vehiclehas been driven for a total of 15199 kilometres since it was new.The table rental has data for each rentalThis records the details for each individual rental of a vehicle.A rental is made by a customer, identified by a unique customer ID. The customer rents a specific vehicle(identified by the VIN).A new tuple is entered into the table rental at the time that the customer picks up the vehicle. The fieldsodo_out and date_out record the odometer reading on the vehicle, and the date. The fields odo_backand date_back are set to NULL (because these values will not be known until the car is returned.)When the car is returned, An UPDATE statement completes the tuple by setting odo_back and date_backto the actual odometer reading on the car and the actual date that the car is returned.This rental is now completed and the rental cost can be calculated from the costs for that type of car,the number of days in the rental (including both the start day and finish day of the rental), and numberof kilometres travelled (the value odo_back – odo_out).Dates are given in the YYYY-MM-DD string format used by SQLite.At this stage, the rental desk clerk is meant to update the odometer field in the tuple in the Vehicletable for this particular car, but sometimes the clerk is busy, puts this off until later, and then forgetsto do it.The table Customer has data for each customerThis table records the details for each Dodgey Brothers Auto Rentals customer.Each customer has a unique id, and Dodgey Brothers Auto Rentals only keeps the name andemail address of their customers. An account can be created for a customer before they rent a car.The tasksAs a database developer, you have been called in to improve the integrity of the database. You will notbe changing any of the column names or data types of the tables, but just adding database features toimprove the integrity and usability of the database.You are asked to submit four filesERD.pngDB.sqlDBTrigger.sqlDBView.sqlaccording to the following Specifications:1. An entity-relationship diagram cssubmit ERD.png (5 marks)The first task is to get a visual representation of the database. This requires you to reverseengineer the actual database to produce the corresponding entity-relationship diagram.Do not invent additional entities or attributes in the ERD, but also remember thatin certainsituationsnot all of the relations in the ERD will be represented as tables in the database. Video31 should clarify what is Required.You must use ERDPlus.com to prepare your ERD and then use the Export Image selection fromthe Menu button at the top-left of a diagram to save it to a PNG file. The file will be savedunder some generic name like image.png, but you should rename it to ERD.png and submit it asthe first file to cssubmit.Include the relevant cardinality and participation constraints according to the specifications above,using your real-world knowledge of how car rentals work for anything not explicitly specified.Once again, do not submit anything that is produced by a different ER diagramming tool, orproduced as a figure in Microsoft Word, or drawn in a drawing/painting program, or is handdrawnand photographed/scanned.(The reason for this is that there are literally hundreds of diagramming tools / conventions, and itwould be impossible for the markers to know them all.)2. A database schema Cssubmit DB.sql (2 + 2 + 2 = 6 marks as specified below)You should prepare a file called DB.sql that creates an improved database. It should contain codeto create the four tables Car, Vehicle, rental and Customer, with exactly the same attributesand data types as described above, but with additional features (as described below).You should only include the DDL statements (the statements that create the tables, views andtriggers) but do not include any statements to insert data into the tables.Of course, you should test your improved database by populating it your own synthetic (made-up)sample data, and running various insert, update and delete commands, but do not include this inyour submission.The additional features you should incorporate into DB.sql are: 5DRAFT(a) Key columns (2 marks)The tables written by Wayne and Arthur Dodgey contain no information about keys, so nothingprevents the accidental insertion of inconsistent data (for example, two different vehicleswith the same VIN).Give improved CREATE TABLE statements for the tables Car, Vehicle and Customer, ensuringthat the uniqueness constraints specified above are enforced by the database.(b) Referential integrity (2 marks)One problem for Wayne and Arthur is that the desk clerk often enters a new tuple into rentalin a hurry, and mistypes either the VIN or the customerId. If the VIN is incorrect, then itis impossible to calculate the cost of a rental, and if the customerId is incorrect, then it isimpossible to know which customer to charge, so this is a major problem.Give an improved CREATE TABLE rental statement to incorporate referential integrity constraintsensuring that the VIN and customerId refer to actual vehicles and customers in theVehicle and Customer tables.Wayne and Arthur tell you that a customer is never deleted from the table, but occasionally acustomerId might change (via an UPDATE statement). If this happens, then the tuples in therental table for this customers previous rentals should automatically be altered to reflectthis change.For vehicles, Wayne and Arthur tell you that the VIN for a vehicle can never change, and avehicle is never deleted from the database.(c) Data entry validation (2 marks)A vehicles VIN is very important for any and all paperwork, such as lease agreements, insurancedetails, servicing schedule etc.However it is easy to mistype a long sequence of characters, and so wed like to add somevalidation to ensure that anything entered into this field at least has the right format to be aVIN. A VIN is a string of exactly 17 characters Each character in a VIN is a digit or an uppercase letter A VIN can contain any of the digits 0 to 9 A VIN can contain any uppercase letter except I, O andQ The 9th character of a VIN is either a digit from0 to9 or the letterX.(These are all true facts about a VIN, but in real VINs the 9th character acts a check digitand must satisfy an equation involving the other 16 characters.)SQLite implements SQL check constraints. A check constraint is a boolean expression associatedwith a single column using the keyword CHECK. Every time the value in that column isaltered (or inserted) the system will check that the boolean expression is still true with thenew value.For example, consider a table BankAccount for an account where the balance is never allowedto drop below 0. This could be defined withCREATE TABLE BankAccount(accountNumber INTEGER,accountBalance REAL CHECK(accountBalance = 0));The system will then check the condition when any UPDATE statement is attempted, and prohibitthe operation if the changed value violates the condition.Add a CHECK constraint to the table Vehicle to ensure that the VIN always meets the basicrequirements above. You may need to look up the documentation for CHECK on sqlite.orgto double-check the exact syntax.3. A trigger to improve data consistency cssubmit DBTrigger.sql (2 marks)Wayne and Arthur constantly have problems keeping the odometer fields in Vehicle and rentalconsistent.6DRAFTAs mentioned previously, when the customer rents a vehicle, a tuple is created in the rental table.At this point, the clerk checks the actual vehicles odometer and enters this value into odo_out.When the customer returns the vehicle, the clerk again checks the vehicles odometer, and entersthis value into the odo_back field for this rental.At this point, the clerk is also meant to update the odometer field in the Vehicle table, so thatboth Vehicle.odometer and rental.odo_back have the same value.However, relying on the desk clerk to transfer values correctly when busy helping customers is notrealistic. You advise Wayne and Arthur that having the same data stored in two different placesis poor relational database design. Wayne and Arthur say that they are unwilling to change theschema because too many other systems rely on it, and ask if you can work around this design flawsome other way.You realise that this is an ideal situation for the use of a trigger.Write the code for a trigger on the table rental that maintains consistency between the twoodometer fields in the following manner: When the desk clerk inserts a new tuple into rental, he or she enters the actual values forthe VIN, customerId and date_out, but enters NULL for the other three values.The trigger should intercept this operation, look up the odometer reading for this car in theVehicle table, and enter this Value into the odo_out column for the newly-created tuple inrental. When the desk clerk updates a tuple in rental (because the customer has returned the car)he or she updates the date_back and odo_back fields with the current date and the actualreading on the vehicles odometer.The trigger should intercept this operation and update the correct row of Vehicle with thenew odometer reading.This ensures that the desk clerk cannot accidentally enter an incorrect odo_out value at the startof the rental, and cannot forget to update the odometer reading in Vehicle at the end of the rental.4. A view to improve usability cssubmit DBView.sql (2 marks)For tax purposes, customers often want a list of all of their rentals together with the cost of eachrental. The necessary SQL command to extract this information in the right format is a littlecomplicated and too easy for Wayne and Arthur to get wrong.Write the SQL code that Defines a view name CustomerSummary that should behave as though itwere a table with each row containing just the essential information about a completed rental.So the view should have the following schema:CustomerSummary (customerId INTEGER,rental_date_out TEXT,rental_date_back TEXT,rental_cost REAL);Write the code to create the view CustomerSummary with the specifications as above.Wayne and Arthur Dodgey also known as The Dodgey Brothers were characters on the Australian TV ComedyShow Australia Youre Standing In It from the early 1990s. 7如有需要，请加QQ：99515681 或邮箱：99515681@qq.com

“