Columbia University - CS 4111hw3_database_2014_solution

Document Content and Description Below

W4111 Homework 3 Solutions – Fall 2014 Q1. a) The given design is not suitable for checking the constraints and executing queries required by the application. It would be very hard or impossible ... to specify the constraints and queries in standard SQL. We are interested in the exonStarts and exonEnds attributes, which are of type "longblob" and stored as a string of comma separated integer values. This means that we would need to do a lot of string processing on these table attributes to get information of interest and standard SQL does not provide any constructs to facilitate this. For example, if we look at the constraints that we want to impose or queries we want to support, we can see that these would be impossible with standard SQL. - We want to be able to check that exonStarts and exonEnds are well formed. For doing this, we could select these attributes using SQL but after that step, we would have to parse the string or split the string on commas to check if it is well formed (essentially, do some sort of string processing), which cannot be specified with standard SQL. - We also want to check that the number of exonStart and exonEnd positions is exactly same. Again, we would need to get the number of integers in the strings corresponding to exonStart and exonEnd attributes, which requires some string processing. - For supporting queries such as total length of all axons, we would want to get every individual integer in exonEnd string and the corresponding integer exonStart string, get their difference and add these up. It is obvious that getting every integer would require string processing. Again, a query asking how many exons the gene contains would have to parse the string and count the number of integers, which is not straight forward with standard SQL. b) We can use Oracle’s nested-table feature to propose the following alternative object-relational design for representing the required information about exons. CREATE TYPE exonType AS OBJECT ( exonStart INTEGER, exonEnd INTEGER ); This study source was downloaded by 100000816398418 from CourseHero.com on 03-28-2021 08:23:15 GMT -05:00 https://www.coursehero.com/file/27130658/hw3-database-2014-solutionpdf/ This study resource was shared via C [Show More]

Last updated: 1 year ago

Preview 1 out of 6 pages