I have attached two assignments related to data management and systems. Kindly provide a l quotation for this order. Deadline is 7th of may 2020 before 1pm.
Microsoft Word - DPIT115 Assignment 2 - Autumn 2020 - FINAL.docx DPIT115 Data Management and Security Final Assessment (Assignment 2) Page 1 of 9 Diploma of Information Technology DPIT115 Data Management and Security Final Assessment (Assignment 2) Autumn Session 2020 This exam represents 40% of the total subject marks _______________________________________________________________________________ Due Date: 7 June 2020, 11:55 PM ________________________________________________________________________________________ DIRECTIONS TO STUDENTS 1. All questions are to be attempted 2. Total number of questions: 7 (SEVEN) 3. This is an individual assessment 4. All questions are to be answered by you without assistance from other people 5. This assessment requires you to submit only one Microsoft Word document 6. Do not include the questions in your answer 7. Your submission will be checked for uniqueness using Turnitin 8. Use your own words, provide your own examples and draw your own diagrams. 9. Show your solution as well as the steps taken to answer the questions 10. Submit your solution to Moodle by the due date. If it is suspected that you have accessed or received additional assistance from another person, 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. Please note, as part of this investigation, you may be required to undergo an oral examination to verify your understanding of the assessment content. DPIT115 Data Management and Security Final Assessment (Assignment 2) Page 2 of 9 QUESTIONS 3, 4, 5, 6 and 7 REFER TO THE RELATIONAL TABLES LISTED BELOW CREATE TABLE EMPLOYEE( ENUM DECIMAL(12) NOT NULL, /* Employee number */ FNAME VARCHAR(50) NOT NULL, /* First name */ LNAME VARCHAR(50) NOT NULL, /* Last name */ DOB DATE NULL, /* Date of birth */ CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(ENUM) ); CREATE TABLE DRIVER( ENUM DECIMAL(12) NOT NULL, /* Employee number */ LNUM DECIMAL(8) NOT NULL, /* Driving license number */ STATUS VARCHAR(10) NOT NULL, /* Driver status */ CONSTRAINT DRIVER_PKEY PRIMARY KEY(ENUM), CONSTRAINT DRIVER_UNIQUE UNIQUE(LNUM), CONSTRAINT DRIVER_FKEY FOREIGN KEY(ENUM) REFERENCES EMPLOYEE(ENUM), CONSTRAINT DRIVER_STATUS CHECK ( STATUS IN ('AVAILABLE', 'BUSY', 'ON LEAVE')) ); CREATE TABLE TRUCK( REGNUM VARCHAR(10) NOT NULL, /* Registration number */ CAPACITY DECIMAL(7) NOT NULL, /* Capacity */ WEIGHT DECIMAL(7) NOT NULL, /* Weight */ STATUS VARCHAR(10) NOT NULL, /* Present status */ CONSTRAINT TRUCK_PKEY PRIMARY KEY(REGNUM), CONSTRAINT TRUCK_STATUS CHECK ( STATUS IN ('AVAILABLE', 'USED', 'MAINTAINED')), CONSTRAINT TRUCK_WEIGHT CHECK ( WEIGHT > 0.0 AND WEIGHT < 500="" ),="" constraint="" truck_capacity="" check="" (="" capacity=""> 0.0 AND CAPACITY < 100 ) ); create table trip( tnum decimal(10) not null, /* trip number */ lnum decimal(8) not null, /* driving license number */ regnum varchar(10) not null, /* truck registration number */ tdate date not null, /* trip date */ constraint trip_pkey primary key (tnum), constraint trip_ckey unique (lnum, regnum, tdate), constraint trip_fkey1 foreign key (lnum) references driver(lnum), constraint trip_fkey2 foreign key (regnum) references truck(regnum) ); create table tripleg( tnum decimal(10) not null, /* trip number */ legnum decimal(2) not null, /* leg number */ departure varchar(30) not null, /* departure city */ destination varchar(30) not null, /* destination city */ constraint tripleg_pkey primary key (tnum, legnum), constraint tripleg_unique unique(tnum, departure, destination), constraint tripleg_fkey1 foreign key (tnum) references trip(tnum) ); dpit115 data management and security final assessment (assignment 2) page 3 of 9 question 1 (10 marks) read and analyse the following specification of a sample database domain. a university would like to create a database to record information about some of its activities. the university offers a number of degrees to students. a degree is described by a unique name, the total number of credit points required to complete a degree, and several requirements that must be satisfied by the future students. the university offers three types of degrees: undergraduate degrees, postgraduate degrees, and graduate certificate. postgraduate degrees are available only for the students who have already completed an undergraduate degree. a description of a postgraduate degree includes a list of acceptable undergraduate degrees. a description of a graduate certificate includes a requirement on the total number of years of professional experience. each degree consists of an ordered sequence of subjects. a description of a subject consists of its number in a sequence and unique code, unique title, total number of credits points a subject is worth, and a list of learning objectives. the university employs academic staff members, tutors and support staff members. a common description of a university employee consists of a unique employee number, first name last name and date of birth. first name, last name and date of birth uniquely identified each employee. additionally, academic staff members and tutors are described by an academic degree achieved. support staff members are described by a list of qualifications acquired in the past together with a date when each qualification has been acquired. the university assigns the academic staff members and tutors to the subjects. a subject has one or two academic members assigned and a number of tutors. academic staff members and tutors can be assigned to many subjects. support staff members are assigned to the degrees. each support staff member is assigned to one degree, and a degree has one or more support staff members assigned. the university is divided into faculties and faculties are divided into schools. academic staff members and tutors belong to one school, and each school consists of many academic staff members and tutors. the faculties and schools are described by the unique names. the university records information when the academic staff members and tutors first join the schools. the university also keeps the information about the former employees who worked at the university in the past. a description of a former employee is the same as a current employee, and additionally, it includes a hire date and end of employment date. (1) draw a conceptual schema for the specification of a database domain listed above. use the notation of uml simplified class diagrams explained to you during this subject. note: you are not allowed to use any artificial identifiers and any attributes that are not mentioned in the specification. use umlet to draw the schema and paste images of your drawings into your microsoft word document. add your name, student number and the date to your diagram. there is no need to provide a detailed analysis of how a conceptual schema has been created. the final conceptual schema expressed in the uml simplified notation classes is subject is sufficient. (7 marks) add two (2) new object classes with at least three (3) attributes each and appropriate associations. the choice of object classes, attributes and associations are up to you; however, these should relate to the existing scenario. use umlet to draw the changes to the schema and paste the second diagram into your microsoft word document. write a text description that explains the additional objects, attributes and associations below the diagram. (3 marks) dpit115 data management and security final assessment (assignment 2) page 4 of 9 question 2 (10 marks) consider the conceptual schema given below. your task is to perform the steps of logical database design, i.e. to transform a conceptual schema given above into a collection of relational schemas. before transforming add the attribute ‘date-made’ to the bus class and ‘home-city’ to the driver class. draw the resulting conceptual schema adding your name, student number and the date to the drawing. use umlet and paste images of your drawings into your microsoft word document. for each relational schema clearly list the names of attributes, primary key, candidate keys (if any), and foreign keys (if any). assume that an association method must be used to implement the generalization. show your working as you step through the process of the transformation. dpit115 data management and security final assessment (assignment 2) page 5 of 9 question 3 (8 marks) write the data definition statements of sql that modify the structures of a database listed on page 2 of this assessment in the way described below. note, that some of the modification may require more than one data definition statements of sql statement. (1) modify the consistency constraint of the sample database such that after the modification, it is possible to record in the database information about the trucks that have a capacity up to and including 220. (2 marks) (2) modify the structure and consistency constraint of the sample database such that after the modification, it is possible 100="" )="" );="" create="" table="" trip(="" tnum="" decimal(10)="" not="" null,="" *="" trip="" number="" */="" lnum="" decimal(8)="" not="" null,="" *="" driving="" license="" number="" */="" regnum="" varchar(10)="" not="" null,="" *="" truck="" registration="" number="" */="" tdate="" date="" not="" null,="" *="" trip="" date="" */="" constraint="" trip_pkey="" primary="" key="" (tnum),="" constraint="" trip_ckey="" unique="" (lnum,="" regnum,="" tdate),="" constraint="" trip_fkey1="" foreign="" key="" (lnum)="" references="" driver(lnum),="" constraint="" trip_fkey2="" foreign="" key="" (regnum)="" references="" truck(regnum)="" );="" create="" table="" tripleg(="" tnum="" decimal(10)="" not="" null,="" *="" trip="" number="" */="" legnum="" decimal(2)="" not="" null,="" *="" leg="" number="" */="" departure="" varchar(30)="" not="" null,="" *="" departure="" city="" */="" destination="" varchar(30)="" not="" null,="" *="" destination="" city="" */="" constraint="" tripleg_pkey="" primary="" key="" (tnum,="" legnum),="" constraint="" tripleg_unique="" unique(tnum,="" departure,="" destination),="" constraint="" tripleg_fkey1="" foreign="" key="" (tnum)="" references="" trip(tnum)="" );="" dpit115="" data="" management="" and="" security="" final="" assessment="" (assignment="" 2)="" page="" 3="" of="" 9="" question="" 1="" (10="" marks)="" read="" and="" analyse="" the="" following="" specification="" of="" a="" sample="" database="" domain.="" a="" university="" would="" like="" to="" create="" a="" database="" to="" record="" information="" about="" some="" of="" its="" activities.="" the="" university="" offers="" a="" number="" of="" degrees="" to="" students.="" a="" degree="" is="" described="" by="" a="" unique="" name,="" the="" total="" number="" of="" credit="" points="" required="" to="" complete="" a="" degree,="" and="" several="" requirements="" that="" must="" be="" satisfied="" by="" the="" future="" students.="" the="" university="" offers="" three="" types="" of="" degrees:="" undergraduate="" degrees,="" postgraduate="" degrees,="" and="" graduate="" certificate.="" postgraduate="" degrees="" are="" available="" only="" for="" the="" students="" who="" have="" already="" completed="" an="" undergraduate="" degree.="" a="" description="" of="" a="" postgraduate="" degree="" includes="" a="" list="" of="" acceptable="" undergraduate="" degrees.="" a="" description="" of="" a="" graduate="" certificate="" includes="" a="" requirement="" on="" the="" total="" number="" of="" years="" of="" professional="" experience.="" each="" degree="" consists="" of="" an="" ordered="" sequence="" of="" subjects.="" a="" description="" of="" a="" subject="" consists="" of="" its="" number="" in="" a="" sequence="" and="" unique="" code,="" unique="" title,="" total="" number="" of="" credits="" points="" a="" subject="" is="" worth,="" and="" a="" list="" of="" learning="" objectives.="" the="" university="" employs="" academic="" staff="" members,="" tutors="" and="" support="" staff="" members.="" a="" common="" description="" of="" a="" university="" employee="" consists="" of="" a="" unique="" employee="" number,="" first="" name="" last="" name="" and="" date="" of="" birth.="" first="" name,="" last="" name="" and="" date="" of="" birth="" uniquely="" identified="" each="" employee.="" additionally,="" academic="" staff="" members="" and="" tutors="" are="" described="" by="" an="" academic="" degree="" achieved.="" support="" staff="" members="" are="" described="" by="" a="" list="" of="" qualifications="" acquired="" in="" the="" past="" together="" with="" a="" date="" when="" each="" qualification="" has="" been="" acquired.="" the="" university="" assigns="" the="" academic="" staff="" members="" and="" tutors="" to="" the="" subjects.="" a="" subject="" has="" one="" or="" two="" academic="" members="" assigned="" and="" a="" number="" of="" tutors.="" academic="" staff="" members="" and="" tutors="" can="" be="" assigned="" to="" many="" subjects.="" support="" staff="" members="" are="" assigned="" to="" the="" degrees.="" each="" support="" staff="" member="" is="" assigned="" to="" one="" degree,="" and="" a="" degree="" has="" one="" or="" more="" support="" staff="" members="" assigned.="" the="" university="" is="" divided="" into="" faculties="" and="" faculties="" are="" divided="" into="" schools.="" academic="" staff="" members="" and="" tutors="" belong="" to="" one="" school,="" and="" each="" school="" consists="" of="" many="" academic="" staff="" members="" and="" tutors.="" the="" faculties="" and="" schools="" are="" described="" by="" the="" unique="" names.="" the="" university="" records="" information="" when="" the="" academic="" staff="" members="" and="" tutors="" first="" join="" the="" schools.="" the="" university="" also="" keeps="" the="" information="" about="" the="" former="" employees="" who="" worked="" at="" the="" university="" in="" the="" past.="" a="" description="" of="" a="" former="" employee="" is="" the="" same="" as="" a="" current="" employee,="" and="" additionally,="" it="" includes="" a="" hire="" date="" and="" end="" of="" employment="" date.="" (1)="" draw="" a="" conceptual="" schema="" for="" the="" specification="" of="" a="" database="" domain="" listed="" above.="" use="" the="" notation="" of="" uml="" simplified="" class="" diagrams="" explained="" to="" you="" during="" this="" subject.="" note:="" you="" are="" not="" allowed="" to="" use="" any="" artificial="" identifiers="" and="" any="" attributes="" that="" are="" not="" mentioned="" in="" the="" specification.="" use="" umlet="" to="" draw="" the="" schema="" and="" paste="" images="" of="" your="" drawings="" into="" your="" microsoft="" word="" document.="" add="" your="" name,="" student="" number="" and="" the="" date="" to="" your="" diagram.="" there="" is="" no="" need="" to="" provide="" a="" detailed="" analysis="" of="" how="" a="" conceptual="" schema="" has="" been="" created.="" the="" final="" conceptual="" schema="" expressed="" in="" the="" uml="" simplified="" notation="" classes="" is="" subject="" is="" sufficient.="" (7="" marks)="" add="" two="" (2)="" new="" object="" classes="" with="" at="" least="" three="" (3)="" attributes="" each="" and="" appropriate="" associations.="" the="" choice="" of="" object="" classes,="" attributes="" and="" associations="" are="" up="" to="" you;="" however,="" these="" should="" relate="" to="" the="" existing="" scenario.="" use="" umlet="" to="" draw="" the="" changes="" to="" the="" schema="" and="" paste="" the="" second="" diagram="" into="" your="" microsoft="" word="" document.="" write="" a="" text="" description="" that="" explains="" the="" additional="" objects,="" attributes="" and="" associations="" below="" the="" diagram.="" (3="" marks)="" dpit115="" data="" management="" and="" security="" final="" assessment="" (assignment="" 2)="" page="" 4="" of="" 9="" question="" 2="" (10="" marks)="" consider="" the="" conceptual="" schema="" given="" below.="" your="" task="" is="" to="" perform="" the="" steps="" of="" logical="" database="" design,="" i.e.="" to="" transform="" a="" conceptual="" schema="" given="" above="" into="" a="" collection="" of="" relational="" schemas.="" before="" transforming="" add="" the="" attribute="" ‘date-made’="" to="" the="" bus="" class="" and="" ‘home-city’="" to="" the="" driver="" class.="" draw="" the="" resulting="" conceptual="" schema="" adding="" your="" name,="" student="" number="" and="" the="" date="" to="" the="" drawing.="" use="" umlet="" and="" paste="" images="" of="" your="" drawings="" into="" your="" microsoft="" word="" document.="" for="" each="" relational="" schema="" clearly="" list="" the="" names="" of="" attributes,="" primary="" key,="" candidate="" keys="" (if="" any),="" and="" foreign="" keys="" (if="" any).="" assume="" that="" an="" association="" method="" must="" be="" used="" to="" implement="" the="" generalization.="" show="" your="" working="" as="" you="" step="" through="" the="" process="" of="" the="" transformation.="" dpit115="" data="" management="" and="" security="" final="" assessment="" (assignment="" 2)="" page="" 5="" of="" 9="" question="" 3="" (8="" marks)="" write="" the="" data="" definition="" statements="" of="" sql="" that="" modify="" the="" structures="" of="" a="" database="" listed="" on="" page="" 2="" of="" this="" assessment="" in="" the="" way="" described="" below.="" note,="" that="" some="" of="" the="" modification="" may="" require="" more="" than="" one="" data="" definition="" statements="" of="" sql="" statement.="" (1)="" modify="" the="" consistency="" constraint="" of="" the="" sample="" database="" such="" that="" after="" the="" modification,="" it="" is="" possible="" to="" record="" in="" the="" database="" information="" about="" the="" trucks="" that="" have="" a="" capacity="" up="" to="" and="" including="" 220.="" (2="" marks)="" (2)="" modify="" the="" structure="" and="" consistency="" constraint="" of="" the="" sample="" database="" such="" that="" after="" the="" modification,="" it="" is="">