c | 代做assignment | sql | 作业database | oracle – COMP 3005

COMP 3005

c | 代做assignment | sql | 作业database | oracle – 这个题目属于一个 c 的代写任务, 是比较典型的 c /sql/database/oracle等代写方向, 这个项目是assignment代写的代写题目

数据库代写 代写oracle oracle代写

assignment # 6

Due: December 2

Instruction

  1. This assignment must be completed on an individual basis, and submit as a single word/PDF file to culearn. Make sure your uploaded file can be opened. If it cannot be opened when marking, you cannot resubmit. Do not email the assignment to the instructor or TAs!
  2. You need to use oracle VM for this assignment.
  3. For the database, Lastname is your own last name. If your own last name is not showing correctly in the result, you will get a mark of 0 for the assignment.

Part 1. Functional Dependencies (15)

  1. Give the following relation. Which of the following dependencies may hold in the above relation? If the dependency cannot hold, explain why by specifying the tuples that cause the violation. ( 5 )
(a) A  B
(b) B  C
(c)  c  B
(d) B  A
(e) C  A
  1. Consider a relation R(A,B,C,D,E) with the following functional dependencies. AB C, CD E, DE B Is AB a key of this relations? If not, is ABD? Explain your answer using the Armstrong inference rules. (5)
  2. Disprove the following inference rules. ( 5 )

a. A B, A C B C

b. AB C B C

Part 2. Nested Relational Databases ( 5 0)

Given the following two nested relations Person and Department where a person has a birthdate and is either a faculty member, a PhD student or a Masters student in a department.

A B C^
a1 b1 c^
a1 b2 c^
a2 b4 c^
a3 b3 c^
a4 b1 c^
a5 b3 c^
  1. Create the two nested relations as shown above by using varrays for PhD, Masters and Offices and
nested tables for Students and Faculty. Put your  sql DDL statements in the assignment document
as well as the screenshots of the two nested relations and all their components obtained with the
describe command: desc Department; desc Faculty_N ; ... ( 10 )
  1. Populate this database using the information in the two nested relations given above. Put your SQL
DML statements as well as screenshots of the two relation contents with command select * from
table_name  in the assignment document. ( 10 )
  1. Use SQL to do the following queries. The attributes for the result should be faculty name and students. The result is 2 marks. (a) list faculty and their Masters students in a nested table ( 5 ) (b) list faculty and all their students in a nested table ( 7 ) (c) list faculty and all their students in a nested table such that they have the same office. ( 8 ) (d) list faculty and all their students in a nested table such that they have the same office and same birth month (10)

Part 3. Object Relational Databases ( 5 0)

  1. Create an object relational database using the information in two nested relations above by properly defining classes Department , Person , and Office and subclasses of Person : Faculty , Student , PhD and Masters to take the advantage of attribute inheritance. This database should have object relations for Office , PhD , Masters , Faculty , and Department so that references are used instead of names or office numbers. Only use a nested relation for attribute Faculty in Department relation and varray for other sets. Note that a student has just one office that is shared with other students but a faculty can have more than one office that may be shared as shown in the nested relation Person in Part 2. (10)

Person (^) Name Office BirthDate Year Month Allan

1230
1975 1
1231
David 1231 1995 1
Henry 1231 1996 2
Emily 1232 1997 3
James 1232 1998 4
Kevin 1232 1999 5
Terry
1233
1980 7
1234
Maria 1233 2000 6

Lastname (^1233 2001 ) Sandy 1235 2002 8 Department Name Faculty Name Students PhD Masters Computer Allan David Henry Emily James Kevin Terry Maria Lastname Sandy

  1. Populate this database properly. Put your SQL DML statements as well as the screenshots of the two relations Office and Department with select * from table_name , and the result query result with select name from table_name for Faculty, Ph.D, and Masters in the assignment document. (10)
  2. Use SQL to redo the four queries and put screenshots of the statements and the result in the assignment document.
(a) list faculty and their Masters students in a nested table (5)
(b) list faculty and all their students in a nested table (7)
(c) list faculty and all their students in a nested table such that they have the same office. (8)
(d) list faculty and all their students in a nested table such that they have the same office and
same birth month (10)