1. (total marks 20) Given the same simple SalesRep-Worksin-SalesArea database schema you worked on in assignment 1, which contains three files described as follows: SalesRep (SRid: integer, SRname: string, SRage: integer, salary: real) Worksin (SRid: integer, Arid: integer, hours: integer) SalesArea (Arid: integer, Aname: string, city: string, budget: real, managerid: integer)

Note : SRid, SRname, SRage, salary are the sales rep id, name, age and salary respectively. Also, hours is the number of hours worked by sales rep in the sales area. The rest of the attributes Arid, Aname, city, budget and managerid are the area id, name, location, budget and managerid respectively. A manager is a sales rep.

Assume that an update operation (a general term in this chapter, for an insert, a modify or a delete operation) is to be made to this database to enter information about a new Salesrep who is now the new manager of an existing SalesArea (replacing the old manager) and although he is new, he has worked some hours in another existing SalesArea. Answer the following questions on what specific relations, attributes and operations (eg. insert, modify, delete) that need to be done for this update to be implemented in the entire database. This is not SQL query yet.

Provide your answers both in descriptive sentence and using the formal (not SQL) database operations of INSERT, MODIFY, DELETE as used in Chapter 5 of book with specific attributes and relations when possible. An example formal insert of a Salesrep record into the Salesrep table is: INSERT < SRidr, SRname, SRage, salary> into SalesRep // for new SalesRep record

And an example descriptive sentence of the formal operation above is: do an insert operation for a new SalesRep record into the SalesRep table.

(a) Give the set of needed insert, modify or delete operations for this update. 5 marks

(b) What types of constraints (explain with data) would you expect to check for this update to be done? 5 marks

(c) Which of the constraints of key, entity integrity, and referential integrity constraints are checked by each of your operations (e.g., Insert) and which are not? 5 marks

(d) Specify all the referential integrity (foreign key) constraints on this database. 5 marks

(Total for que 1 is 20 marks)

Solution:

Question	Answers
a. Give the operations for this update. 5 marks
b. What types of constraints would you expect to check? 5 marks
c. ) Which of the constraints of key, entity integrity, and referential integrity constraints are checked by each of your operations (e.g., Insert) and which are not? 5 marks
d. Specify all the referential integrity constraints on this database. 5 marks	We will write a referential integrity constraint as R.A --> S (or R.(X) --> T) whenever attribute A (or the set of attributes X) of relation R form a foreign key that references the primary key of relation S (or T).

(total marks 10) Consider the following relation:

Enrolled(Studid, Crsid, Ctitle, Score, Lettergrade)

Assume that a student (Studid) may be enrolled in multiple courses (Crsid) and hence {Studid, Crsid } is the primary key. Thus, the following functional dependency exists:

{Studid, Crsid } -> {Ctitle, Score, Lettergrade}

Additional functional dependencies are:

Crsid -> Ctitle

Score -> Lettergrade

Based on the given primary key and the dependencies,

1. Is this relation in 1NF, 2NF, or 3NF? Why or why not?
2. If not in 2NF at least, normalize it completely into 2NF and 3NF? Provide your answers using all functional dependencies (FDs) in this database that apply to your argument. (Total for que 3 is 10 marks)

Solution (i): (5 marks)

Answer:

Solution (ii) (5 marks)

please answer both of this questions.