Download Database Integrity and Security Exam Questions for NUI Galway, 2009/2010 and more Exams Data Communication Systems and Computer Networks in PDF only on Docsity!
Ollscoil na hÉireann, Gaillimh GX_____ National University of Ireland, Galway Semester 1 Examinations, 2009/ Exam Code(s) 2BF 3BC 4BC2, 4BC3, 4BC4, 4BC Exam(s) Bachelor of Science in Business Information Systems Bachelor of Commerce Bachelor of Commerce (International) Module(s) MS218 Database Technologies MS210 Database Systems (repeat candidates only) External Examiner(s) Professor Hans van der Heijden Internal Examiner(s) Dr. Tom Acton Dr. Michael Lang (Internal telephone contact: extension 3515) Instructions Answer Question 5 and any other 2 questions. Duration 2½ hours FOR INFORMATION OF EXAMINATIONS OFFICE ONLY Paper No. 1 of 1 Repeat Paper N/A^ Special Paper N/A No. of Answer Books
N/A
Requirements Handout None MCQ None Statistical Tables None Graph Paper None Log Graph Paper None Other Material None No. of Pages 9 Department(s) Accountancy & Finance
- An essential requirement of a relational database management system (RDBMS) is that it should assure data integrity. Explain, using clear examples and SQL code where applicable, how the following data integrity constraints can be implemented in a RDBMS environment (e.g. MySQL): (a) Referential Integrity (including definition of foreign keys) (10 marks) (Suggested time: 15 minutes) (b) Transaction Management SQL commands (7 marks) (Suggested time: 10 minutes) (c) Definition of a required field where the value must be one of a specified list (8 marks) (Suggested time: 10 minutes)
- Illustrate using examples (with diagrams, text, and SQL code) how each of the following would be implemented in SQL-compliant relational database management systems (RDBMS): (a) One-to-One relationships (1:1) (7 marks) (Suggested time: 10 minutes) (b) Many-to-Many relationships (M:M) (10 marks) (Suggested time: 15 minutes) (c) Recursive One-to-Many relationships (8 marks) (Suggested time: 10 minutes)
- (a) Below is an example of a person’s profile as stored in a paper-based file of movie information in a video rental store. Using the technique of normalisation , you are required to produce a data model that is capable of being implemented in a relational database. At each stage of the transformation from un-normalised form (0NF) to third normal form (3NF), clearly explain what changes you make, and why these are necessary. Name Robert de Niro Date of Birth August 17, 1943 Place of Birth New York City, NY, USA Oscar Awards as Best Actor The Godfather Part II (1974), Raging Bull (1980) Selected Filmography as Actor Film Name Role Year The Godfather Part II Vito Corleone 1974 Taxi Driver Travis Bickle 1976 The Deer Hunter Michael 1978 Raging Bull Jake La Motta 1980 Goodfellas James 'Jimmy' Conway 1990 Cape Fear Max Cady 1991 A Bronx Tale Lorenzo Anello 1993 Casino Sam 'Ace' Rothstein 1995 Heat Neil McCauley 1995 Ronin Sam 1998 Meet The Parents Jack Byrnes 2000 The Score Nick Wells 2001 Analyze That Paul Vitti 2002 Selected Filmography as Director Film Name Year A Bronx Tale 1993 The Good Shepherd 2006 (20 marks) Suggested time: 30 minutes (b) In designing a database, it is important to carefully reflect upon assumptions and exceptions as these can impact decisions such as the choice of keys, the cardinality of relationships, and integrity rules. Briefly discuss this statement. (5 marks) Suggested time: 5 minutes
- Given the tables and relationships depicted in the schema below, you are required to provide the SQL commands necessary to execute the transactions (a) to (m) which follow. Notes: It is recommended that you clearly state any assumptions that you make. Any valid dialect of SQL is acceptable , provided that you are consistent throughout. A number of hints are provided to assist along the way, but you don’t have to follow these. Marks will not be lost if you choose to implement your solution in a different way that works. If you produce a solution which works but is unnecessarily cumbersome , some marks will be deducted for failing to produce an acceptably elegant solution. A larger version of the database schema is reproduced on the last page of this examination paper. You may find it helpful to detach that page from the rest of the examination paper.
g) The great-great-granddaughter of Michael Quinn, who was born in 1832 ( YearOfBirth ) and who lived at 15 Main Street, Drumshanbo, County Leitrim ( HouseNum, Street, TownlandName, County ) at the time of the 1861 census ( CensusYear ) has requested that you produce a list of all the full addresses that he was known to reside at throughout his life. The output should list the census year, house number, street, townland, barony name, and county. You should sort this list in descending order of year. (4 marks) h) Produce a list showing the barony name and total area of all the baronies in County Sligo, excluding the barony of Tireragh ( BaronyName ). [Hint: To calculate the area of a barony, add up the Hectares of all townlands located within that barony. Use a grouped query.] (4 marks) i) What is the name ( BaronyName) and size in hectares of the largest barony in County Westmeath? [Hint: See query (h) ]. (4 marks) j) List the names of all townlands and the baronies and counties within which they are located, where the Gaelic name of the townland ( GaelicName ) contains the word “Muileann” or “Mhuilinn” (meaning a mill) anywhere in the name, and there was at least one person resident in that townland at the time of the 1881 census ( CensusYear ) whose listed occupation was a “Miller” or “Flour Merchant” ( Occupation ). The query results should be sorted in ascending order of county followed by ascending order of barony and townland name. No row should be listed more than once in the output i.e. eliminate duplicate rows. [Hint: Use pattern matching on the GaelicName field] (5 marks)
k) For all of the counties of Ireland at the time of the 1911 census ( CensusYear ), list the name of the county and the number of persons resident in that county who could speak Irish. In your output, the second column should be titled NumIrishSpeakers. Exclude counties where the number of Irish speakers is less than 250. The output of the query should be sorted firstly by descending order of the NumIrishSpeakers column, then by ascending order of County name. [Hint: Use a query which counts the applicable records based on the CanSpeakIrishYN field in the PersonalCensusData table, grouping the output by the County field] (5 marks) l) For all of the baronies in County Tipperary, list the name and total population of that barony in 1841 and 1851, as well as the change in population. The column headings should be labelled County , Pop1841 , Pop1851 , PopChange. [Hint: Create two separate views, one for 1841 and one for 1851 ( CensusYear ), which count the applicable records in the PersonalCensusData table, grouping the output by the BaronyName field. You can then link the two views together by joining them on the BaronyName field, and subtract the value of Pop from Pop1851 to compute the PopChange .] (5 marks) m) John O’Gara, whose father of the same name was born in Roscommon ( PlaceOfBirth ) in 1898 ( YearOfBirth ), wants to find out the places of birth, years of birth, and names at birth ( FirstName , LastNameAtBirth ), of his paternal grandparents i.e. his father’s mother and father. [Hint: One way of doing this is to firstly create a view showing PersonID and all that person’s mother’s details, and to create a separate view which shows PersonID and that person’s father’s details. Both views would need to be based on queries which implement the appropriate recursive relationship. The two views could then be linked together in the final output query] (6 marks) (Total marks for Question 5: 50 marks) Suggested time: 80 minutes
