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
constr
...
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]
 V1-V2.png)
