83%(12)10 out of 12 people found this document helpful
This preview shows page 22 - 23 out of 26 pages.
Page 43 of 52Phone Number Occupation Part a Create the missing table that will allow us to search for information in these tables (Give the table a name). Show the primary keys in each table by circling them. Draw a line showing the relationships from one table to the other. Part b Write an SQL statement that would list all of the possible dates people can choose from. Part c Write an SQL statement to list all of the people who have requested the second week of the summer. They should be in order by Last Name. Part c Write an SQL statement to list all dates that Mike Myers has requested. Part d Write an SQL statement that will list all of the doctors who have requested the week of August 14. Part e Write an SQL statement to show all Customers who have the same last name. Question 76Suppose that we need a database to keep track of friendships between men and women. There’s no limit on the number of male friends a woman might have, or vice versa, so friendship between men and women is clearly a many-to-many relationship. We might use a database like the following: Part a What SQL statement would I use to print the names of every one of Steve’s girl friends, sorted by name, given that Steve’s ID Number is 42? Part b What SQL statement would I use to list the names of every couple in which the man has brown eyes and the woman has blue eyes? Recall the FavouriteColour table from Assignment 8, where it was explained that SQL can produce a list of the people whose favourite colour is the same as person 42’s via the following select statement, which uses the FavouriteColour table twice. select B.pkey, B.name, B.colour from FavouriteColour as A, FavouriteColour as B where A.pKey = 42 and (A.colour = B.colour) Part c What SQL statement would I use to list the names of every pair of men who have a common girl friend with red hair? Part d How could you re-organize the data in our Men/Friends/Women database into two tables and still be able to answer the same questions? It is sufficient to simply draw your new tables, provided the table and field names are well-chosen, but feel free to explain your design briefly if you think that will help the marker understand your answer. Using this new database, write an SQL statement that list the names of every couple in which the man has brown eyes and the woman has blue eyes. (That is, do part b again, but for your new 2-table database.) Question 77Part a Why is data validation important to consider when you're creating a database? Give two distinct examples of data validation techniques, and give an example of a field that you would apply each of them to. Part b When you are designing a relational database, what two important clues indicate that you should place information in a separate table, and why? Part c You are given a project to do, and you know that you want to use either a spreadsheet or a database. How do you decide which one to use? Question 78It's your job to design a database to keep track of video rentals. For each CS200 Study QuestionsPage 44 of 52