New version page

assignment

Upgrade to remove ads

This preview shows page 1-2-3 out of 8 pages.

Save
View Full Document
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience

Upgrade to remove ads
Unformatted text preview:

1 Carnegie Mellon University 15‐415 Database Applications Spring 2012, Faloutsos Assignment 1 : ER + Formal Q.L. Due: 2/07, 1:30 pm, in class – hard copy Reminders  Weight: 5% of the homework grade.  The points of this homework add up to 100.  Like all homeworks, this has to be done individually.  Lead TA: U Kang.  Rough time‐estimates: 2~4 hours (1 to 2 hours for questions 1~3, and 1 to 2 hours for questions 4~6).  Please type your answers. Illegible handwriting may get no points, at the discretion of the graders. Only drawings may be hand‐drawn, as long as they are neat and legible. Remember that:  Whenever you are making an assumption, please state it clearly. 2 Question 1 : Olympic Database [10 points] We want to design a database for storing the information of U.S. athletes and their participations in Olympic games.  An athlete has his/her SSN, the name, and the birth date.  Each Olympiad has the year, the country, and the city. For example, the 2008 Olympic game was held in Beijing, China.  An athlete can participate in several sports in an Olympiad. For example, Michael Armstrong participated in the swimming, diving, and water polo events.  Every athlete in our data base participated in at least one Olympiad. Every Olympiad has at least one athlete (and actually, several thousands). Q1.1 Draw an ER diagram for this database. Make sure to indicate primary keys, cardinality constraints, weak entities (if any), and participation constraints. [5 points] Q1.2 Turn the ER diagram into tables: give SQL statements to create the tables. Make sur e to indicate primary keys, foreign keys, if any. Specify the appropriate integrity constraints to enforce the total/partial coverage, and overlap constraints, if any. [5 points] Question 2 : Cuisine Database [10 points] The student administrator of the Mini University wants to design a database for profiling students’ preferences for the nearby restaurants and the dishes. For example, the student Jack Smith likes the “rib eye steak” at the “TGI Fridays”, not the “rib eye steak” at the “Olive Garden”, while the student Nancy Graham likes the Italian spaghetti at the “Union Grill”.  Each student has his/her SSN, the name, and the department.  Each restaurant has its name, and the mailing address.  The name of a restaurant may not be unique, but the mailing address is unique.  Each dish has the name and the price.  The price of a dish might diffe r on different restaurants. For ex ample, “rib eye steak” is $15 at “TGI Fridays”, but $20 at the “Olive Garden”.  The name of a dish is unique within a restaurant. Two restaurants may have the same dish name.  A dish is offered by at least one restaurant, and a restaurant offers at least a dish. Q2.1 Draw an ER diagram for this database. Make sure to indicate primary keys, cardinality constraints, weak entities (if any), and participation constraints. [10 points] 3 Question 3 : Student Database [20 points] We want to design a student database for the Mini University.  A student has his/her SSN, the name, and the age.  Some students belong to the School of Engineering, and we want to store their E‐SAT (Engineering SAT) scores.  Some students belong to the School of Fine Art, and we want to store their FA‐SAT (Fine Art SAT) scor es.  There are other schools (e.g. School of Humanities and Social Sciences) at the Mini University, but we don’t explicitly record the school, for anybody.  The Mini University allows students to have double majors. Thus, a student could be in both the School of Engineering (with an E‐SAT) as well as in the School of Fine Arts (with an FA‐SAT). Q3.1 Draw an ER diagram showing the class hierarchies. Make sure to indicate primary keys. [5 points] Q3.2 Are the constraints in this hierarchy overlapping constraints? Give your answers and the reasons. [5 points] Q3.3 Are the constraints in this hierarchy covering constraints? Give your answers and the reasons. [5 points] Q3.4 Turn the ER diagram into tables: give SQL statements to create the tables. Make sur e to indicate primary keys and foreign keys, if any. Specify the appropriate integrity constraints to enforce the total/partial coverage, and overlap constraints, if any. [5 points] 4 Question 4 : Relational Algebra [20 points] A network intrusion refers to an activity where an attacker sends malicious packets to a victim to do malicious things (e.g. crash the victim machine, or gain root access of the victim machine.) A network security company monitors the network and records the network intrusion events. For the purpose, it has the following two tables in its database. The table MACHINE contains the attacker or victim information and it has the following three fields:  mname (the machine name of the attacker or the victim; e. g. jupiter)  ipaddr (the ip address of the attacker or the victim; e.g. 9.9.9.9)  os (the operating system of the attacker or the victim; e.g. Linux, Windows). The primary key of the MACHINE table is ipaddr. The table EVENT contains the attack information and it has the following three fields:  a-ipaddr (the ip address of the attack er; e.g. 9.9.9.9)  v-ipaddr (the ip address of the victim; e.g. 10.10.10.2)  date (the date the attack was performed; e.g. 2/7/2012). The primary key of the EVENT TABLE includes all three fields. Q4.1 (Victim list) The compan y wants to find the victim ip addresses that were attacked on 2/7/2012. Write the relational algebra expression. [5 points] Q4.2 (Attacker profile) The company wants to find the operating systems used for attacking the victim ’10.10.10.2’. Write the relational algebra expression. [5 points] Q4.3 Consider the following EVENT table: EVENT a-ipaddr v-ipaddr date 9.9.9.1 10.10.10.2 2/1/2012 9.9.9.2 10.10.10.1 2/2/2012 9.9.9.2 10.10.10.3 2/2/2012 9.9.9.9 10.10.10.4 2/3/2012 9.9.9.9 10.10.10.3 2/4/2012 9.9.9.10 10.10.10.3 2/5/2012 9.9.9.10 10.10.10.4 2/6/2012 Consider the following expression: 5 a‐ipaddr,v‐ipaddr(EVENT)÷v‐ipaddr[a‐ipaddr='9.9.9.9'(EVENT)] Q4.3.1 Describe in English what the query tries to


Download assignment
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view assignment and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view assignment 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?