Exercis 1
1.List significant differrences between a file-processing system and a DBMS.
2.What are the advantages and disadvantages of databa?
3.Explain the difference between physical and logical data independence.Discuss the ANSI-SPARC three-level architecture.
4. What is a data model? Discuss the main types of data models.
An integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on the data in an organization. (See Section 2.3.)
Object-bad data models such as the Entity–Relationship model (e Section 2.3.1). Record-bad data models such as the relational data model, network data model, and hierarchical data model (e Section 2.3.2). Physical data models describe how data is stored in the computer (e Section 2.3.3).
Answer:
• Physical data independence is the ability to modify the physical scheme without making it necessary to rewrite application programs. Such modifications include changing from unblocked to blocked record storage, or from quential to random access files.
• Logical data independence is the ability to modify the conceptual scheme without making it necessary to rewrite application programs. Such a modification荠菜炒年糕 早教是什么might be adding a field to a record; an application program’s view hides this change from the program.
Exercis 2税务师
1.The following tables form part of a databa held in a relational DBMS:
Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)
where Hotel contains hotel details and hotelNo is the primary key;
Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key;
Booking contains details of the bookings and (hotelNo, guestNo, dateFrom) forms the primary key;
and Guest contains guest details and guestNo is the primary key.
Identify the foreign keys in this schema. Explain how the entity and referential integrity rules apply to the relations.
For each relation, the primary key must not contain any nulls.
Room is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Room is FK，and should either be null or contain the number of an existing hotel in the Hotel relation. In this ca study, it would probably be unacceptable to have a hotelNo in Room with a null value.
东南形胜
芦蒿炒腊肉 Booking is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Booking is FK， and should either be null or contain the number of an existing hotel in the Hotel relation. However, becau hotelNo is also part of the primary key, a null value for this attribute would be unacceptable. Similarly for guestNo. Booking is also related to Room through the attribute roomNo,(guestNo,roomNo) in booking is FK reference Room. guestNo in booking is FK reference Guest.
Describe the relations that would be produced by the following relational algebra operations:
(a) hotelNo (σprice > 50 (Room) )
This will produce a relation with a single attribute (hotelNo) giving the number of tho hotels with a room price greater than £50.
(b)σHotel.hotelNo还原作用 = Room.hotelNo(Hotel ⨯ Room)
This will produce a join of the Hotel and Room relations containing all the attributes of both Hotel and Room (there will be two copies of the hotelNo attribute). Esntially this will produce a relation containing all rooms at all hotels.
Produce some sample tables for the relations that obrve the relational integrity rules. Suggest some enterpri constraints that would be appropriate for this schema.
Student should provide some sample tables, obrving entity and referential integrity. In particular, ensure the uniqueness for the composite primary keys of the Room and Booking tables.
Some enterpri constraints may be:
∙There can be no two bookings for the same room in the same hotel on the same day.
字母图∙For the Booking relation, dateFrom must be before dateTo.
∙Room price must be greater than 0 and less than £200.
3.Consider the relational databa of Figure 1,
employee (person-name, street, city)
works (person-name, company-name, salary)
春去冬来company (company-name, city)
manages (person-name, manager-name)
Figure 1. Relational databa
where the primary keys are underlined.Give an expression in the relational algebra to express each of the following queries:
a.Find the names of all employees who work for First Bank Corporation.
b. Find the names, street address, and cities of residence of all employees who
work for First Bank Corporation and earn more than $10,000 per annum.
c. Find the names of all employees in this databa who live in the same city
