c/c++代写
java代写
python代写
MATLAB代写
作业代写
c/c++代写,java代写,python代写,matlab代写,作业代写,留学生作业代写
CSIT815 Data Management and Security
School of Computing and Information Technology Session: Autumn 2018 University of Wollongong Lecturers: Janusz R. Getta Tianbing Xia CSIT815 Data Management and Security Project 19 March 2018 Scope This project addresses the problems in database design, database implementation, and data security. Important messages Please read the messages listed below before implementation of the tasks included in a specification of the project. More implementation related information can be found in “How to … ?” Cookbook available through Moodle or at: http://www.uow.edu.au/~jrg/115/COOKBOOK. The outcomes of the project are due by Saturday, 2 June, 2018, 10.00 pm sharp. The project contributes to 4% of the total evaluation in the subject. A submission procedure is explained at the end of this document. Only one submission of the project is allowed and only one submission per student is accepted. Please make sure that you submit the correct files. A submission that contains an incorrect file attached is treated as a correct submission with all consequences coming from the evaluation of the file attached. Compressed (zipped, rared, tared, etc) files will not be evaluated. A submission marked by Moodle as “late” is treated as a late submission no matter how many seconds it is late. The reports from processing of SQL scripts must return NO ERRORS ! A solution with the errors is worth no marks ! A policy regarding late submissions is included in CSIT115/815 Subject Outline. Tasks Task 1 (1 mark) Consider the following specification of a sample database domain. A transportation company provides door-to-door delivery of parcels and needs a database to keep track of the business activities. The company uses an infrastructure based on the public transportation model. The customers submit the parcels to safe storage containers set up at railroad stations and thousands of other places across the country. Each parcel obtains a unique bar code, which is automatically scanned when a parcel is dropped into a container. Vehicles travel over the route segments, pick up the parcels from the containers and either leave the parcels in another container or deliver the parcels to the destinations. The database must contain information about: • All route segments, described by a unique segment number and a length. One route segment may span over several roads. Each road has a unique road identifier and it is described by the names of cities located at its endpoints. • All endpoints of route segments, including their geographical coordinates (longitude and latitude). A pair of endpoints also uniquely identifies a route segment. A route segment is connected to zero or more other route segments at its endpoint. • The name and address of each customer that has ever used the service. More than one customer may reside at any address. • The vehicles owned by the company. For every vehicle, the type and model name, along with information on carrying capacity, and mileage. The registration number is unique for each vehicle. • Name and driver license number of every driver. • For each safe storage container, its geographical coordinates (longitude and latitude), capacity, and the date it was put to use. Storage containers are located along and at the connections of route segments. • Information about every parcel, including the customer who ordered the shipping, shipping price, order date, delivery address, and the unique bar code. • Information about the present location of each parcel. The location of a parcel is either the location of safe storage container or the location of a vehicle used for transportation of a parcel. • Information about present location of each vehicle and its driver. Location of a vehicle includes its geographical coordinates (longitude and latitude) and the route segment currently travelled. The company expects that it will later extend the descriptions of customers and parcels with more specific attributes. Your design must be constructed such that there will be no need to change it in the future no matter and what and how many attributes are added. Design a conceptual schema for the database, which will keep information about World Tour Championships in Greasy Pole Climbing (WTC-GPC) competitions. Use the notation of simplified UML class diagrams explained during the lecture classes and applied for conceptual modeling in the laboratories and assignments. Remember to determine the multiplicities of associations, identifiers of classes of objects, multivalued, optional and derived attributes (if any). Make sure that a schema of your database does not contain redundancies and it correctly represents all information that should be included in the database. You are allowed to apply any design methodology, which is the most convenient for you. Only the final conceptual schema is expected and there is no need to show a design process. You are allowed to add more realworld attributes to the design, however, it is NOT allowed to add any artificial attributes, which do not exist in the reality, like for example addressID or vehicleID, etc. You are also allowed to decide on the identifiers of classes, however your choice must be consistent with the reality. Use UMLetlet to draw a conceptual schema of the designed database and when ready export it to a file solution1.pdf. Deliverables A file solution1.pdf with the final design of a conceptual schema. Task 2 (1 mark) Perform a logical design to convert a conceptual schema created in the previous task into a collection of relational schemas and next implement SQL script task2.sql that to create the relational tables. Next, include into a script task2.sql SQL statements that insert from 3 to 5 rows into each one of the relational tables created. Finally, add to your script SELECT statements that list the contents of all relational tables in the database. Execute a script file task2.sql and save a report from processing of the script in a file solution2.rpt. Deliverables A file solution2.rpt with a report from processing of SQL script task2.sql. Task 3 (1 mark) Create SQL script task3.sql that implement the following queries. All constants in SELECT statements are up to you, however, the constants must be selected such that all queries return nonempty answers. It also means, that you may have to return to the previous task to add more rows to the relational tables. (1) List full information about a selected route segment. The selected route segment must span over at least 2 roads. List the descriptions of roads the selected route segments spans on. (2) List the names of drivers who passed through a given route segment at least 2 times. (3) List the names and addresses of the customers together with the total number of times each one of them was used transportation service. (4) List full description of vehicles that have not been used for any service yet. (5) List the locations of storage containers together with the full descriptions of parcels stored in each container. Execute a script file task3.sql and save a report from processing of the script in a file solution3.rpt. Deliverables A file solution3.rpt with a report from processing of SQL script task3.sql. Task 4 (1 mark) Assume that the managers of the transportation company are divided into the following groups: mngmt, admin, and driver. Implement SQL script task4.sql that creates three users such that one user belongs to a group mngmt, one user belongs to a group admin, and one user belongs to a group driver. A group mngmt must have read and write access rights on all relational tables in a database. A group admin must have read access rights on all relational tables in a database and write access right on the relational tables that included information about customers and parcels. A group driver must have read access rights on all tables excluding the tables that contain information about customers and read and write access rights on all tables that contain information about trucks, parcels, and storage containers. Add to SQL script task4.sql the statements that implement access control system described above and execute a script task4.sql. Save a report from processing of a script task4.sql in a file solution4.rpt. Deliverables A file solution4.rpt with a report from processing of SQL script task4.sql. Submission Note, that you have only one submission. So, make it absolutely sure that you submit the correct files with the correct contents. No other submission is possible ! Submit the files solution1.pdf, solution2.rpt, solution3.rpt, and solution4.rpt through Moodle in the following way: (1) Access Moodle at http://moodle.uowplatform.edu.au/ (2) To login use a Login link located in the right upper corner the Web page or in the middle of the bottom of the Web page (3) When logged select a site CSIT815/CSIT115 (S117) Data Management and Security (4) Scroll down to a section Submissions (5) Click at a link In this place you can submit the outcomes of Project(CSIT815 students only) (6) Click at a button Add Submission (7) Move a file solution1.pdf into an area You can drag and drop files here to add them. You can also use a link Add… (8) Repeat step (7) for the files solution2.rpt, solution3.rpt, and solution4.rpt. (9) Click at a button Save changes (10)Click at a button Submit assignment (11)Click at the checkbox with a text attached: By checking this box, I confirm that this submission is my own work, … in order to confirm the authorship of your submission (12)Click at a button Continue It is expected that all its tasks included within the project will be solved individually without any cooperation with the other students. If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes or office hours. Plagiarism will result in a FAIL grade being recorded for that assessment task. End of specification
留学生作业代写,cs作业代写,cs代写,作业代写,北美cs作业代写,澳洲cs作业代写,加拿大cs作业代写,cs作业代写价格,靠谱cs作业代写,程序代写