1. What is a foreign key? What conditions must be satisfied for the values of a foreign key?

Foreign key (FK) is defined as follows:

i) Consider two relation schemas R1 and R2;

ii) The attributes in FK in R1 have the same domain(s) as the primary key attributes PK in R2; the attributes FK are said to reference or refer to the relation R2;

iii)A value of FK in a tuple t1 of the current state r(R1) either occurs as a value of PK for some tuple t2 in the current state r(R2) or is null. In the former case, we have t1[FK] = t2[PK], and we say that the tuple t1 references or refers to the tuple t2.

  1. List the types of Microsoft Access Action Queries.

Make-table query

Delete query

Update query

Append query

  1. Specify the following concepts.

The cardinality constraint for a binary relationship specifies the number of relationship instances that an entity can participate in.

Three kinds of cardinality constraints: 1:N, 1:1, M:N

The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type.

Two kinds of participation relationships: total and partial

A weak entity type is an entity type, for which the primary key cannot be recognized.

An identifying relationship exists between a weak entity type and its owner.

  1. Consider the following ERD. Map the ERD to a relational database showing the relation(s) that will be created (show relation names, attributes, and primary keys).

Department: [DeptNo, Manager_fName, Manager_lName]

Phone: [DeptNo, phone]

Office: [DeptNo, office]

  1. When you use the relationships tool in Microsoft Access, the system indicates whether the relationship is one-to-many or one-to-one. How is the system able to determine when the relationship must be one-to-one?

  1. Consider the following ERD.

Now, consider the recursive relationship “a person is the child of another person”.

a)Modify the ERD to accommodate this relationship.

b)Show the relations (show the relation name, attributes, and primary key) that exist when you map your ERD in part a) above to a relational database.

Suppose Sam and Silvia are children of John and Jane. Make up other data for these people and illustrate the contents of your relation(s). Show the rows that must exist in your relation(s).

c)Show how your answer to part a) appears when the MS Access relationships tool is used.

Person:

1 / John / … …
2 / Jane / … …
3 / Sam / … …
4 / Silvia / … …

ChildOf:

3 / 1
3 / 2
4 / 1
4 / 2
  1. When you create a form in Microsoft Access, there are various types of elements (such as controls, combo boxes, and so on) that can be placed on the form. List 4 different types of elements and briefly describe the purpose of each.
  1. Microsoft Access Reports have seven components/sections, beginning with the Report Header. Name the other six sections.

Page Header

Group Header

Details

Group Footer

Page Footer

Report Footer

  1. Consider the following Vehicle table.

Vid

/ Make / Model / Colour / Kilos / Year / Price /

Sold

1 / Toyota / Celica / Red / 100000 / 2000 / $15,000.00 / Yes
2 / Honda / Prelude / Blue / 150000 / 2004 / $18,000.00 / No
3 / Toyota / Celica / Red / 145000 / 2003 / $15,500.00 / Yes
4 / Honda / Prelude / Blue / 100000 / 2005 / $22,000.00 / Yes
5 / Toyota / Celica / Red / 100000 / 2005 / $15,000.00 / No
6 / Honda / Prelude / Blue / 150000 / 2005 / $18,000.00 / Yes
7 / Toyota / Celica / Red / 145000 / 2001 / $15,500.00 / Yes
8 / Honda / Prelude / Blue / 100000 / 2002 / $22,000.00 / No

a)A business analyst would like to see the results of a query that shows the vehiclessold in 2004 or in 2005. Design your query and show its definition in Design View.

b)Another analyst wants a crosstab query illustrating how many vehicles there are for the various colours and years. Show the result (include row headings, column headings) of running such a query for the sample data.

Answer a)

Year / Red / Blue
2000 / 1 / 0
2001 / 1 / 0
2002 / 0 / 1
2003 / 1 / 0
2004 / 0 / 1
2005 / 1 / 2
  1. Given the query below, show the corresponding Query Design View.

Use an attached sheet for your answer.

Assume that table Students contains attributes: SSN, FName, Lname, Credits, QualityPoints. The query will have three calculated attributes:

-Name. The value of this attribute is the concatenation of FName, ‘,’, and Lname.

-GPA. The value of this attribute is computed by dividing QualirtPoints by credits.

-Distinct. The value of this attribute is computed a follows:

If GPA is >= 3.5, the value is “Dean’s list”

Else if GPA > 2.0, the the value is “N/A”

Else the value is “Probation”.

  1. Consider the following design.

Map this ERD to a relational database (show relation names, attributes, and primary keys)

Person: [Pid, Name]

Marriage: [Mid, Mdate, husband, wife]

  1. Consider the following ERD.

a)Illustrate the relationships as they would be shown in the MS Access Relationships Tool.

b)Show the relation structure created for Product when the ERD is mapped to a relational database.