Part 2 The Relational Model and Languages
Chapter 4 The Relational Model
4.1 Describe the main characteristics of the Relational Data Model, including the properties of relations and the rules for relational integrity.
Relational
– set of tables (as perceived by users)
– rows/columns
– variety of possible storage structures
– no repeating groups
– no links/pointers
– use of join columns.
– no duplicate tuples
– tuples are unordered
– attributes are unordered
– all attribute values are atomic.
Entity Integrity: No attribute participating in the PK of a base relation is allowed to accept null values.
Referential Integrity: If a base relation R2 includes a FK targeting the PK of R1, then every value of FK must either be:
– equal to the value of PK in some tuple of R1 or
– wholly null.
4.2 The Relational and CODASYL models are examples of two different approaches by which a Database Management System will be classified. Make a detailed comparison of each of these models, indicating clearly the relative advantages and disadvantages of each.
CODASYL
– records in chains/rings
– parent-child links
– many parents for a child
– ordering of all records
– fixed access paths
– potential for automatic referential integrity.
Relational
– set of tables (as perceived by users)
– rows/columns
– variety of possible storage structures
– no repeating groups
– no links/pointers
– use of join columns.
CODASYL Data Model Advantages
– longevity and availability of DBMSs for this model
– all data relationships can be modeled
– standards exist
– good performance
– data independence.
CODASYL Data Model Disadvantages
– complex navigation
– complexity.
Relational Data Model Advantages
– simplicity
– high level of data independence
– strong theoretical foundation.
Relational Data Model Disadvantages
– performance needs improvement
– referential integrity not supported.
4.3 Describe the difference between a base relation and a view and discuss the main benefits of using views in a relational database.
A base relation is a named relation, corresponding to an entity in the conceptual schema, whose tuples are physically stored in the database. A view can be constructed by performing operations such as the relational algebra selection, projection, join or other calculations on the values of existing base relations. A view is the dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not actually exist in the database but is produced upon request by a particular user, at the time of request.
The main benefits of views includes:
• It provides a powerful and flexible security mechanism by hiding parts of the database from certain users. The user is not aware of the existence of any attributes or tuples that are missing from the view.
• It permits users to access data in a way that is customized to their needs, so that the same data can be seen by different users in different ways, at the same time.
• It can simplify complex operations on the base relations. For example, if a view is defined as a join of two relations, the user may now perform the more simple unary operations of selection and projection on the view, which will be translated by the DBMS into equivalent operations on the join.
4.4 From an SQL user’s perspective, does the relational model provide logical and physical data independence?
Since a user can define views, logical data independence can be achieved by using view definitions to hide changes in the conceptual schema. Since the SQL user has no knowledge of how the data is physically represented, relying solely on the relation abstraction for querying, physical data independence is also achieved.
Chapter 5 Relational Algebra and Relational Calculus
5.1 Choose any four relational algebra operators and explain how each functions.
For example: Select – produces a horizontal subset of a relation. Project produces a vertical subset of a relation by picking out particular attributes. Product (Cartesian product) produces a result relation by multiplying one relation by another. If the first relation contained 20 rows and the second 50, the result relation would contain 20 × 50 rows. Join produces a result relation by (commonly) joining two relations over the equal value of an attribute common to both relations. Examples should be given for all operators explained.
5.2 Given two relations R and S, where R contains N1 tuples and S contains N2 tuples (N2 > N1 > 0), give the minimum and maximum cardinality for the result relation for each of the following relational algebra expressions and in each case state any assumptions about the schemas that are required to make the expression meaningful:
(a) R S
(b) R S
(c) R – S
(d) R x S
(e) a = 1(R)
(f) a(R)
Answer is shown in table below.
Expression Min Max Assumptions
R S N2 N1 + N2 R and S must be union -compatible
R S 0 N1 R and S must be union –compatible
R – S 0 N1 R and S must be union –compatible
R x S N1 * N2 N1 * N2 No restrictions
a = 1(R) 0 N1 R must have an attribute called a
a(R) 1 N1 R must have an attribute called a
The relation Journey records the price of an adult fare from Paisley to the given destination by as specified operator, several operators may operate over the same route.
Formulate the following queries using relational algebra, tuple relational calculus, and domain relational calculus (the answers to these queries in SQL are given in the next section):
(a) List the details of journeys less than £100.
RA: price < 100(Journey)
TRC: {J | Journey(J) J.price < 100}
DRC: {opCode, destinationCode, price |Journey(opCode, destinationCode, price)
price < 100)}
(b) List the names of all destinations.
RA: destinationName(Destination)
TRC: {D.destinationName | Destination(D) }
DRC: {destinationName | (destinationCode, distance)
(Destination(destinationCode, destinationName, distance))}
(c) Find the names of all destinations within 20 miles.
RA: destinationName(distance < 20(Destination))
TRC: {D.destinationName | Destination(D) D.distance < 20}
DRC: {destinationName | (destinationCode, distance)
(Destination(destinationCode, destinationName, distance) distance < 20)}
(d) List the names of all operators with at least one journey priced at under £5.
RA: opName(price < 5(Journey) 3 opCode Operator)
TRC: {O.opName | Operator(O) (J) (Journey (J) (O.opCode = J.opCode) J.price < 5)}
DRC: {opName | (opCode, opCode1, destinationCode, price)
(Operator(opCode, opName) Journey(opCode1, destinationCode, price)
(opCode = opCode1) price < 5)}
(e) List the names of all operators and prices of journeys to ‘Ayr’.
RA: opName, price ( (destinationName = ‘Ayr’(Destination) 3 destinationCode (Journey 3 opCode Operator))
TRC: {O.opName, J.price | Operator(O) (J)(D) (Journey (J) Destination(D)
(O.opCode = J.opCode) (J.destinationCode = D.destinationCode)
D.destinationName = ‘Ayr’)}
DRC: {opName, price | (opCode, opCode1, destinationCode, destinationCode1,
destinationName, distance) (Operator(opCode, opName)
Journey (opCode1, destinationCode, price)
Destination(destinationCode1, destinationName, distance)
(opCode = opCode1) (destinationCode = destinationCode1)
destinationName = ‘Ayr’)}
(f) List the names of all destinations that do not have any operators.
RA: destinationNamedestinationCode(Destination) – destinationCode(Journey)) 3 destinationCode Destination)
TRC: {D.destinationName | Destination(D) (~(J) (Journey (J)
(J.destinationCode = D.destinationCode))) }
DRC: {destinationName | (destinationCode, distance)
(Destination(destinationCode, destinationName, distance)
(~(opC, destinationCode1, price) (Journey(opC, destinationCode1, price)
(destinationCode = D.destinationCode1)))}
5.4 The following tables form part of a database held in a Relational Database Management System:
Employee (empID, fName, lName, address, DOB, sex, position, deptNo)
Department (deptNo, deptName, mgrEmpID)
Project (projNo, projName, deptNo)
WorksOn (empID, projNo, hoursWorked)
where Employee contains employee details and empID is the key.
Department contains department details and deptNo is the key. mgrEmpID identifies the employee who is the manager of the department. There is only one manager for each department.
Project contains details of the projects in each department and the key is projNo (no two departments can run the same project).
and WorksOn contains details of the hours worked by employees on each project, and empID/projNo form the key.
(1) List all employees.
RA: Employee
TRC: {E | Employee(E) }
DRC: {empID, fName, lName, address, DOB, sex, position, deptNo |
Employee(empID, fName, lName, address, DOB, sex, position, deptNo) }
(2) List all the details of employees who are female.
RA: sex = ‘F’(Employee)
TRC: {E | Employee(E) E.sex = ‘F’}
DRC: {empID, fName, lName, address, DOB, sex, position, deptNo |
Employee(empID, fName, lName, address, DOB, sex, position, deptNo)
sex = ‘F’}
(3) List the names and addresses of all employees who are Managers.
RA: fName, lName, address(position = ‘Manager’(Employee))
TRC: {E.fName, E.lName, E.address | Employee(E) E.position = ‘Manager’}
DRC: {fName, lName, address | (empID, DOB, sex, position, deptNo)
(Employee(empID, fName, lName, address, DOB, sex, position, deptNo)
position = ‘Manager’}
(4) Produce a list of the names and addresses of all employees who work for the ‘IT’ department.
RA: lName, address(deptName = ‘IT’(Department) 3 deptNo Employee)
TRC: {E.lName, E.address | Employee(E) (D)(Department(D)
(D.deptNo = E.deptNo ) D.deptName = ‘IT’)}
DRC: {lName, address | (empID, fName, DOB, sex, position, deptNo, deptNo1, deptName, mgrEmpID) (Employee(empID, fName, lName, address, DOB, sex, position, deptNo)
Department(deptNo1, deptName, mgrEmpID) (deptNo = deptNo1) deptName = ‘IT’)}
(5) Produce a list of the names of all employees who work on the ‘SCCS’ project.
RA: fName, lName (projName = ‘SCCS’(Project) 3 projNo (WorksOn 3 empID Employee))
TRC: {E.fName, E.lName | Employee(E) (P) (W) (Project(P) WorksOn(W)
(E.empID = W.empID ) (W.projNo = P.projNo) P.projName = ‘SCCS’)}
DRC: {fName, lName | (empID, address, DOB, sex, position, deptNo, projNo,
projName, deptNo1, empID1, projNo1, hoursWorked)
(Employee(empID, fName, lName, address, DOB, sex, position, deptNo)
Project(projNo, projName, deptNo1) WorksOn(empID1, projNo1,
hoursWorked) (empID = empID1) (projNo = projNo1)
projName = ‘SCCS’)}
5.5 The following tables form part of a database held in a Relational Database Management System:
Employee (empNo, eName, salary, position)
Aircraft (aircraftNo, aName, aModel, flyingRange)
Flight (flightNo, from, to, flightDistance, departTime, arriveTime)
Certified (empNo, aircraftNo)
where Employee contains details of all employees (pilots and non-pilots) and empNo is the key.
AirCraft contains details of aircraft and aircraftNo is the key.
Flight contains details of the flights and flightNo is the key.
and Certified contains details of the staff who are certified to fly an aircraft, and empNo/aircraftNo form the key.
Formulate the following queries in relational algebra, tuple relational calculus, and domain relational calculus (the answers to these queries in SQL are given in the next section).
(1) List all Boeing aircraft.
RA: aName = ‘Boeing’(Aircraft)
TRC: {A | Aircraft(A) (A.aName = ‘Boeing’)}
DRC: {aircraftNo, aName, aModel, flyingRange |
Aircraft(aircraftNo, aName, aModel, flyingRange) (aName = ‘Boeing’)}
(2) List all Boeing 737 aircraft.
RA: aName = ‘Boeing’ aModel= ‘737’ (Aircraft)
TRC: {A | Aircraft(A) (A.aName = ‘Boeing’) (A.aModel = ‘737’)}
DRC: {aircraftNo, aName, aModel, flyingRange |
Aircraft(aircraftNo, aName, aModel, flyingRange)
(aName = ‘Boeing’) (aModel = ‘737’)}
Reviews
There are no reviews yet.