HI hope you are doing well this is my assignment of data management and security which is due on sunday I want you to do this for me everything is mentioned attached document just simple 5 tasks you guys did same kind ojf tasks for me before as well you might need my detailsSTUDENT NAME: AHMER NASIRSTUDENT ID: 6880101STUDENT MAIL:
[email protected] mention this details if requiredread out document carefully and submit me file in exact forms mentioned in document all supporting documents are attached please make sure submit exact documents because I don't have time to change and system as well
Microsoft Word - Assignment 1 - Data modelling and database design with UML.docx Diploma of Information Technology Session: Autunm 2021 UOW College Australia DPIT115 Data Management and Security Assignment 1 Published on 25 March 2021 Scope This assignment is related to conceptual modelling of the sample database domain, extension of a given conceptual schema and logical design. There are also tasks related to taking a logical design and implementation of CREATE TABLE statements of SQL. Please read the information listed below very carefully. This laboratory contributes to 15% of the total evaluation in a subject DPIT115. The outcomes of the assignment work are due by Sunday 11 April 2021, 11:55 PM. This assignment consists of 5 tasks. A submission procedure is explained at the end of the specification. A submission in an incorrect file format, filename format, or as a compressed file (zipped, gzipped, rared, tared, 7-zipped, lhzed, … etc) 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. All files left on Moodle in a state "Draft(not submitted)" will not be evaluated. An implementation that does not compile due to one or more syntactical errors scores no marks. It is expected that all tasks included in Assignment 1 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. If it is suspected that you have received assistance from another person to complete the tasks, the matter will be investigated as an alleged breach of the UOW College Academic Integrity and Student Conduct Policy, in accordance with the Procedure for Managing Alleged Student Misconduct. As part of this investigation, you may be required to undergo an oral examination to verify your understanding of the assessment content. Tasks Task 1 (2.5 marks) Read and analyse the following specification of a sample database domain. A transportation company would like to create a database to record some of its operations. The company owns and uses a number of busses to implement transportation services between the cities. The company employs administration staff members, drivers and mechanics. A common description of an employee consists of a unique employee number, first name last name and date of birth. Additionally, drivers are described by a unique driving licence number and category of a driving licence. Mechanics are also described by a list of qualifications acquired in the past together with a date when each qualification has been acquired. The company supports a number of transportation services. Each service starts at a city, passes through a number of intermediate stops and ends in another or the same city. An origin location, a sequence of intermediate stops, and a destination location of a service are described by unique addresses. An address consists of a city name, street name and building number. Each intermediate stop has a unique number in a sequence of intermediate stops within a transportation service. A transportation service is described by a planned departure time from an origin location, the planned arrival times and departure times at the intermediate stops and a planned arrival time a destination location. A city of departure, planned departure time, and a city of destination uniquely identify each transportation service. The transportation company assigns the drivers and busses to the transportation services. Shorter transportation services have one bus and one driver assigned. Longer transportation services have a single bus and multiple drivers assigned. Each driver is assigned to a section of a longer transportation service, A section starts at a location, and it includes a number of successive locations a transportation service passes through. A description of a bus consists of a registration number, make, model fuel consumption and a total number of seats available. An objective if this task is to construct a conceptual schema for the specification of a database domain listed above. Artificial identification attributes, commonly known as "id" attributes are not allowed in your solution. Use the UMLet tool to create a drawing of a conceptual schema in the notation of UML simplified class diagrams explained to you during the lecture classes in DPIT115. No other notation will be accepted. Remember to use the CSIT115-815Palette palette. Add your name, student number and the date to your diagrams. Use an option File->Export as… to export your diagram into a file solution1.bmp in BMP format. Do not delete an exported file. You will submit it as one of the deliverables from your laboratory work. In this task there is NO NEED to provide a detailed analysis of a conceptual schema like in the previous laboratory task. The final conceptual schema expressed in a notation of UML simplified class is entirely sufficient. Deliverables A file solution1.bmp with the final design of a conceptual schema. Submission of a file with a different name and/or different extension and/or different type scores no marks. Task 2 (1.5 mark) Consider the following conceptual schema. The schema represents a database domain where the students submit the assignments and assignments consist of tasks. Your task is to extend the schema such after the extension, it would be possible to store the following information in the database. (1) We would like to store in the database information about two different types of students: postgraduate students and undergraduate students. Postgraduate students are additionally described by a title of research project. Undergraduate students are described by an optional title of software project. (2) We would like to store in the database information about the titles and topics of all software projects and the groups of students who participate in each project. Assume that a title uniquely identifies each project. (3) We would like to add information about the total number of tasks included in each assignment and for each task we would like to store information about a name of file of each task and a sequence number of a task within an assignment. Do not forget about an identifier of a class TASK. Add your name, student number and the date to your diagrams. Use UMLet and CSIT115-815Palette palette to extend the conceptual schema. The original schema is provided in a file task2.uxf. After all extensions save an extended conceptual schema in a file solution2.uxf. Next, use an option File->Export as… to export your diagram into a file solution2.bmp in BMP format. Do not delete the exported file. You will submit it as one of the deliverables from your laboratory work Deliverables A file solution2.bmp with an extended conceptual schema. Submission of a file with a different name and/or different extension and/or different type scores no marks. Task 3 (2 marks) Consider a conceptual schema given below. Assume that each driver has a different number of mobile phone or does not have a mobile phone at all. Also, assume that skills can be shared by mechanics. Your task is to perform logical database design, i.e. to transform a conceptual schema given above into a collection of relational schemas. For each relational schema created clearly list the names of attributes, primary key, candidate keys (if any), and foreign keys (if any). Assume, that the subset method must be used to implement a generalization (if any). A way how a conceptual schema can be transformed into a collection of relational schemas is explained in a presentation 06 Logical Design. The relational schemas must be listed in a format presented in the slides 44 and 45 in a presentation 06 Logical Design. Listing of the relational schemas in the other format scores no marks. Deliverables A file solution3.pdf with a list of relational schemas, the primary key for each relational schema, candidate keys (if any) for each relational schema, foreign keys (if any) for each relational schema. Submission of a file with a different name and/or different extension and/or different type scores no marks. Task 4 (6 marks) Review the lecture material and references tests and form answers to following questions using your own words, diagrams and examples: (1) Discuss each of the following concepts in the context of the relational data model: a. relation b. attribute c. domain d. tuple e. intension and extension f. degree and cardinality (2) Discuss the difference between the candidate keys and primary keys? (3) Explain what is meant by a foreign key? (4) Database design is quite complex and important. Discuss the role played by users during the design process (5) Describe the purpose of normalizing data (6) Briefly describe the basic SQL DDL statements and explain their use Deliverables The file questions4.pdf with your answers to the questions, your name student number and the date, should be included on each page. Submission of a file with a different name and/or different extension and/or different type will score no marks.