This is an exam. Can anyone do my Database Fundamentals exam? Last semester also TFTH has given my exam. But still I failed in the subject. I did not question back then, now I am contacting you 5 days before only, so that you will find a good expert for exam.
La Trobe University Study Period 1 Examination Period 2020 RESTRICTED USE This paper MUST NOT BE REMOVED from the examination venue Subject code: CSE2DBF/CSE4DBF Paper number: 01 Reading time: 15 minutes Writing time: 300 minutes Number of pages: 17 (including cover sheet) Campus: ☒ Sydney ☒ Bendigo ☒ Bundoora ☐ City ☐ Mildura ☐ Shepparton Allowable materials: Number: 18 Description: Students from non‐English speaking backgrounds can bring unmarked, nonelectronic translation dictionaries into the examination (non‐subject specific). Number: Description: Number: Description: Number: Description: Number: Description: Instructions to candidates: 1. This paper consists of 9 questions (5 multiple-choice questions and 4 descriptive questions). They are worth 180 marks. 2. Students need to answer all questions. 2 Multiple-Choice Question 1. (ER/EER Diagram) – 10 marks (Note that there is only one correct answer. The second-best answer will be awarded 5 marks.) LaTrobe Travel agency has different travel packages. Each package has a unique package code. It also has information on different departure dates in the current year. In addition, the duration of each package will be stored. Each package can consist of many destinations. The destination information includes the country and the continent. For each destination, different packages will have a different number of days. LaTrobe Travel also needs to store information regarding the provider of each package. Each travel package can only be served by one provider. The database needs to store information on the provider as well such as the ID and the name. Which of the following ER diagrams correctly represents the above problem statement? (Note that for simplicity, participation have been omitted from the diagrams). (a) visit provide PACKAGE 1 M PackageCode DESTINATION PROVIDER Country ProviderID M 1 Continent ProviderName Dep_Date Number_ of_days Duration (b) visit provide PACKAGE M M PackageCode DESTINATION PROVIDER Country ProviderID M 1 Continent ProviderName Dep_Date Number_ of_days Duration (c) 3 visit provide PACKAGE M M PackageCode DESTINATION PROVIDER Country ProviderID M 1 Continent ProviderName Dep_Date Number_ of_days Duration (d) visit provide PACKAGE M M PackageCode DESTINATION PROVIDER Country ProviderID M 1 Continent ProviderName Dep_Date Number_ of_days Duration Multiple-Choice Question 2. (ER/EER Diagram) – 10 marks (Note that there is only one correct answer. The second-best answer will be awarded 5 marks.) “Snooty Fashions” is an exclusive fashion designer business. Snooty Fashions collaborates with several fashion designers to design outfits. For every fashion designer, a unique ID, name, city of residence and his/her major style are recorded. Snooty Fashions also maintains information of tailoring technicians that work on their outfits. For each tailoring technician, a unique ID, name and year of experience is recorded. Snooty Fashions work with many designers and tailoring technician, some of which are freelance professionals. Snooty Fashions, however, also hires some of designers and tailoring technicians as employees with unique staff ID. Snooty Fashions sponsors its employees to join up to five fashion associations. The database needs to store information of Fashion Associations. The date when the employees join the associations need to be stored as well. Which of the following ER diagrams is the best representation of the above problem statement? (Note: PK attributes are underlined). 4 (a) TAILORS DESIGNERS EMPLOYEES ASSOCIATIONS Sponsor in U employeeID designerID designerName assocID assocName N (1,N) N (0,5) dateJoin tailorID tailorName cityBased yearExp majorInfluence (b) TAILORS DESIGNERS EMPLOYEES ASSOCIATIONS Sponsor in d employeeID designerID designerName assocID assocName N (1,N) N (0,5) dateJoin tailorID tailorName cityBased yearExp majorInfluence 5 (c) TAILORS DESIGNERS EMPLOYEES ASSOCIATIONS Sponsor in U employeeID designerID designerName assocID assocName N (0,N) N (0,5) dateJoin tailorID tailorName cityBased yearExp majorInfluence (d) ASSOCIATIONSSponsor in employeeID designerID designerName N (0,N) N (0,5) dateJoin tailorID tailorName cityBased yearExp majorInfluence Is-aEMPLOYEES DESIGNERS TAILORS 6 Multiple-Choice Question 3. (ER/EER Diagram) – 10 marks (Note that there is only one correct answer. The second-best answer will be awarded 5 marks.) A publisher publishes books in two formats, namely printed book and e-book, while for some other titles only printed book is available. If the book is printed, the information of the number of printed copies is required as well as printed copy price. For e-book, the information of the price is also maintained. In addition, for e-book, the publisher wants to keep track on the platforms in which the e-book can be viewed and downloaded. For each platform, information about platform name and description is required. For each book, the book ID, title, number of pages and release date are required. In addition, the genre of the book is also stored, in which each book can belong to more than one genre. Which of the following ERE diagram is the best representation of the above problem statement? (Note: PK attributes are underlined). (a) BOOK PRINTED E-BOOK Title NumberPages ReleaseDate NumberofCopies PrintedPrice EBookPrice BookID GenreGenre can be N (0,N) N (0,N) N (0,N) PLATFORMviewedon PlatformName PlatformDesc N (1,N) N (1,N) (b) BOOK PRINTED E-BOOK PLATFORMviewedon d Title NumberPages ReleaseDate PlatformName PlatformDesc NumberofCopies PrintedPrice EBookPrice N (1,N) N (1,N) BookID GenreGenre 7 (c) BOOK PRINTED E-BOOK PLATFORMviewedon O Title NumberPages ReleaseDate PlatformName PlatformDesc NumberofCopies PrintedPrice EBookPrice N (1,N) N (1,N) BookID GenreGenre (d) BOOK PRINTED E-BOOKPLATFORM viewedon O Title NumberPages ReleaseDate PlatformName PlatformDesc NumberofCopies PrintedPrice EBookPrice N (0,N) N (1,N) BookID GenreGenre 8 Multiple-Choice Question 4. (Relational Algebra) - 10 marks (Note that there is only one correct answer. The second-best answer will be awarded 5 marks.) Consider the following tables with their instances: CLIENT CLIENT-NO LNAME FNAME ADDRESS PHONE AREA PREF-TYPE MAX-RENT 107 Smith John 731 Plenty Road 923145 5 Clayton House 1,200 232 Wong Franklin 638 Voss Street 975694 5 Preston Flat 650 133 Zelaya Alicia 3321 Castle Ave 986705 5 Balwyn Flat 500 154 Wallace Jennifer 291 Berry Street 923453 6 Preston Townhouse 850 179 Narayan Ramesh 975 Fire Road 945673 8 Carlton Flat 550 VIEWING PROPERTY- NO VIEWING-CLIENT- NO DATE-VIEW COMMENT P122 232 28/02/12 Too small P123 107 12/03/12 P124 232 20/03/12 Excellent P125 107 10/04/12 Too small P125 179 11/04/12 P125 154 12/04/12 Good List the final Result (table instances) of the following relational algebra expression: Temp (VIEWING VIEWING-CLIENT-NO=CLIENT-NO CLIENT) Result CLIENT-NO, PROPERTY-NO, DATE-VIEW (TEMP) 9 (a) CLIENT-NO PROPERTY-NO DATE-VIEW 232 P122 28/02/12 107 P123 12/03/12 232 P124 20/03/12 107 P125 10/04/12 179 P125 11/04/12 154 P125 12/04/12 179 null null (b) CLIENT-NO PROPERTY-NO DATE-VIEW 232 P122 28/02/12 107 P123 12/03/12 232 P124 20/03/12 107 P125 10/04/12 179 P125 11/04/12 154 P125 12/04/12 (c) CLIENT-NO PROPERTY-NO DATE-VIEW 232 P122 28/02/12 107 P123 12/03/12 232 P124 20/03/12 107 P125 10/04/12 179 P125 11/04/12 154 P125 12/04/12 133 null null 10 (d) CLIENT-NO PROPERTY-NO DATE-VIEW 232 P122 28/02/12 107 P123 12/03/12 232 P124 20/03/12 107 P125 10/04/12 179 P125 11/04/12 154 P125 12/04/12 133 P125 12/04/12 Single-Choice Question 5. (Relational Algebra) – 10 marks (Note that there is only one correct answer. The second-best answer will be awarded 5 marks.) Specify the following query using relational algebra expressions. Retrieve the names of artists who have written the words and music to at least one song they have recorded (a) Results artistName (((Artist) artistID = artistID (Record)) songID = = songID (Song)) (b) 11 Results artistName ((Artist) artistID = wordsBy AND artistID = musicBy (Song)) (c) ArtistWM songID, artistID, artistName ((Artist) artistID = wordsBy AND artistID = musicBy (Song)) Results artistName ((ArtistWM) artistID = artistID AND songID = songID (Record)) (d) SongRec songID, artistID, wordsBy, musicBy ((Song) songID = songBy (Record)) Results artistName ((SongRec) artistID = artistID (Artist)) 12 Question 6 (Transformation to Relational Tables) – 25 marks The following EER diagram models the database for a credit union. You need to do these following tasks: ACCOUNT TRANSACTION LEDGER FIXED DEPOSIT REGULAR ACCOUNT CUSTOMER participates edits record d custID custName custAddress custPhone ledgerCode transID transType transDate transAmount recordedDate maxWithdraw term N (1,2) 1 (1,N) N (1,1) 1 (0,N) N (0,1) N (1,N) account# balance interestRate STAFFstaffid name address salary remark JUNIORSENIOR d serviceBonus createS createJ N (0,1) 1 (0,N) N (0,1) 1 (0,N) a) Perform the transformation of the EER model into the relational tables. For specialization relationship, you need to apply option 8A (multiple tables for superclass and sub classes). [20 marks] b) Technically, can you apply transformation rules options 8B, 8C and 8D for specialization relationships of this EER model? What are the implications of applying these three options to your database? [5 marks] 13 Question 7. (Normalization) [30 marks] a) Consider the following functional dependencies of a PROJECT database. ProjName → ProjStartDate ProjName, ProjType → Priority, ProjBudget ProjTeam, PersonName → PersonRole PersonName → PersonContact, PersonSkill Priority → ProjBudget Currently, the database is implemented in UNF as follows. PROJECT (ProjName, ProjStartDate, Priority, ProjType, ProjBudget, (ProjTeam, ProjTeamLeader, (PersonName, PersonRole, PersonContact, PersonSkill))) You need to normalize the PROJECT un-normalized form to arrive at a suitable database design, which satisfies the third normal form. Clearly show your transformation stages and state assumptions whenever necessary. [10 marks] b) Consider the following functional BOOK database, which shows the book information including the authors and the publisher information. Book# Title AuthorName AuthorContact Publisher PublisherCity BookType Price($)