关键词 > COMP2350/6350

COMP2350/6350 Database Systems – Week 3

发布时间：2022-08-25

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

COMP2350/6350 Database Systems

Tutorial – Week 3

The background knowledge for the tutorial questions is given in the textbook(s), lectures, any other components of the unit, in the readings provided on iLearn, and in the prerequisite unit ISYS114 or COMP1350. However, some questions cannot be answered without prior independent research and searching for other sources of information. Of the questions below, the (*) starred ones are core questions, and your tutor will try to discuss them before discussing other questions. Note that there might not be enough time to discuss all the questions in the class

Consider the following relational schema:

Employee(empID, SSN, emp_name, age, salary)

Works(empID, deptID, start_date)

Dept(deptID, dept_Name, budget, managerID)

1. Explain the following terms in the context of the above relational schema: Relation, Attribute, Domain, Tuple, Degree and Cardinality.

Relation: A relation is a table with columns and rows.

Attribute: An attribute is a named column of a relation.

Domain: A domain is the set of allowable values for one or more attributes. Tuple: A tuple is a row of a relation.

Degree: The degree of a relation is the number of attributes it contains. Cardinality: The cardinality of a relation is the number of tuples it contains.

2. (*)

a. Explain the following terms in the context of the relational data model. Candidate Key, Primary Key and Foreign Key.

Candidate Key: A superkey such that it has no proper subset which is a superkey within the

relation.

Primary Key: The candidate key that is selected to identify tuples uniquely within the relation.

Foreign Key: An attribute or set of attributes within one relation that matches the candidate key of some (possibly the same) relation.

b. Complete the following table using the above relational schema:

Relation	Candidate Key	Primary Key	Foreign Key
Employee	empID SSN	empID	None
Works	empID, deptID	empID, deptID	empID, deptID
Dept	deptID	deptID	managerID

3. (*)

a. What is the purpose of nulls and what do they represent?

Nulls are a way to deal with incomplete or exceptional data. They represent a value for an attribute that is currently unknown or is not applicable for this tuple.

b. Is any component of a primary key allowed to accept nulls? Why, or why not?

By definition, a primary key is a minimal identifier that is used to identify tuples uniquely. This means that no subset of the primary key is sufficient to provide unique identification of tuples. If we allow a null for any part of a primary key, we are implying that not all of the attributes are needed to distinguish between tuples, which contradicts the definition of the primary key.

c. What is the term for this constraint (b)?

Entity Integrity Constraint

d. Use the above relational schema to discuss why it is desirable to enforce this constraint.

Class discussion.

4. (*)

a. Describe the constraint called referential integrity constraint.

If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null.

b. What is the purpose of this constraint?

Referential integrity ensures that relationships between tuples in related relations are consistent and valid. Ensures that related tuples are not accidentally deleted or changed.

c. Use the above relational schema to discuss why it is desirable to enforce referential

integrity.

Class discussion.

5. (*) Background reading for this question is available from:

http://www.mysqltutorial.org/introduction-sql-views.aspx

http://www.mysqltutorial.org/create-sql-views-mysql.aspx

a. Explain the view SeniorEmp that is deﬁned in the following SQL code:

CREATE VIEW SeniorEmp(emp_name, age, salary)

AS SELECT E.emp_name, E.age, E.salary

FROM Employee E

WHERE E.age > 50

Class discussion.

There are restrictions on types of modifications that can be made through views:

• Updates are allowed if query involves a single base relation and contains a candidate key of the base relation.

• Updates are not allowed involving multiple base relations; Though some DBMS such as Oracle supports updates involving multiple base relations in recent time.

• Updates are not allowed involving aggregation or grouping operations.

b. Give an example of a view on Employee that could be automatically updated.

The following view on Employee can be updated automatically by updating Employee.

CREATE VIEW SeniorEmp (empID, emp_name, age, salary)

AS SELECT E.emp_ID, E.emp_name, E.age, E.salary

FROM Employee E

WHERE E.age > 50

c. Give an example of a view on Employee that would be impossible to update (automatically) and explain why your example presents update problem that it does.

CREATE VIEW AvgSalaryByAge (age, avgSalary)

AS SELECT E.empID, avg(E.salary)

FROM Employee E

GROUP BY E.age

(For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table but there isn’t one. Also there is both a grouping operation and an aggregation operation in the view. )