数据库原理（双语）

数据库原理（双语）课后作业

会在这个博客里面不定时更新老师平时作业的！！！！！！！！

Exercises NO7:

数据库中关系如下：
student (Sno, Sname, Sdept, DOB, Address, Telephone, Gender, Degree)
lecturer (Tno, Tname, Department, Address, Telephone, Title)
course (Cno, Cname, Cpno,Credit)
contact (Tno, Cno, Hours)
enrol (Sno, Cno, Mark)

1、List the numbers, names and departments of all the students who enrolled in the course ‘ACSC7101’(the number of course)

ΠSno, Sname, Sdept (Cno=’ACSC7101’(student∞enrol))
ΠSno, Sname, Sdept (student∞Cno=’ACSC7101’(enrol))

2、List the information(including numbers, names and marks) of the CS department’s students who enrolled in the course ‘ACSC7102’ and their mark is higher than 90.

ΠSno, Sname, Mark (Cno=’ACSC7102’^{sdept=‘CS’}MARK>90(student∞enrol))
ΠSno, Sname, Mark (Cno=’ACSC7102’^{Sdept=’CS’}Mark>’90’(student∞enrol))

3、List the information(including numbers, names and departments) of the students who are from MA department or enrolled in the course ‘ACSC7103’.

ΠSno, Sname, Sdept (Cno=’ACSC7103’vSdept=’MA’(student∞enrol))
ΠSno, Sname, Sdept (Sdept=’MA’(student)) ∪ΠSno, Sname, Sdept (Cno=’ACSC7103’ (student∞enrol))
ΠSno, Sname, Sdept (Cno=’ACSC7103’vSdept=’MA’(enrol∝student))

4、List the numbers, names and departments of the students who enrolled in the course Database Theory.

ΠSno, Sname, Sdept (Cname=’Database Theory’(student∞enrol∞course))
ΠSno, Sname, Sdept (student∞enrol∞Cname=’Database Theory’(course))
ΠSno, Sname, Sdept (Cname=’Database Theory’(student∞(course∞enrol)))

5、Answer the question “who(by name, department and title) are the lectures of the course Data Structure ?”

ΠTname, Department, Title (Cname=’Data Structure’(lecturer∞contact∞course))

6、List all the courses(numbers and names) which the postgraduat (the degree is ‘postgraduat’) enrolled in.

ΠCno,Cname (degree=’postgraduate’(student∞enrol∞course))

7、List the students(by name) who enrolled in at least one of the courses Peter Smith has enrolled? ÷
ΠSname,Cno(student∞enrol) ∞ΠCno(Sname=’Peter Smith’(student∞enrol))
ΠSname,Cno (Sname<>’Peter Smith’(ΠSno (enrol∞ΠCno(Sname=’Peter Smith’(student∞enrol))) ∞ student))

List the students(by name) who enrolled in all the courses Peter Smith has enrolled?
ΠSname,Cno(student∞enrol) ÷ΠCno(Sname=’Peter Smith’(student∞enrol))

8、Find the students who has not enrolled in the course ‘ACSC7102’(course number).

ΠSno,Sname(student)- ΠSno,Sname (Cno=’ACSC7102’(student∞enrol))

9、List all the courses(by name) taught by the teacher whose title is ‘professor’.

ΠCname,(Title=’professor’(lecturer∞contact∞course))
ΠCname (Title=’professor’(lecturer) ∞contact∞course)

10、List all the students who are born after 2000/1/1.

ΠCno,Cname(DOB>’2000/01/01’(student))

课堂测验

Test of mudule02
A. For each of the following questions there are four choices marked A, B, C and D. Only one of them is correct. You should decide on the correct choice and write down its mark on the answer sheet. (4 points each, total 28 points).

A 1．Which one of the following is not the property of relations?
A．Each attribute has a distinct data type. B．Each attribute has a distinct name.
C．Order of rows has no significance. D．Order of cols has no significance.
D 2．In relational models, each relation can only have one .
A．candidate key B．foreign key C．inner key D．primary key
C 3．In relational models, each candidate key .
A．can only have at least two attributes B．can only have at most one attributes
C．may have one or more attributes D．must contain all the attributes of the relation
C 4． Given a relation schema R and its attributes X, if X references a primary key Y of relation schema S, then we call .
A．X is a foreign key of relation schema S B．Y is a foreign key of relation schema R
C．X is a foreign key of relation schema R D．Y is a foreign key of relation schema S
A 5．Suppose Set D1, D2 and D3 have K1, K2 and K3 elements, respectively, then the number of tuples in D1D2D3 will be ______.
A．K1 K2 K3 B．K1+ K2+ K3 C．(K1+ K2)K3 D．(K1+ K2)÷K3
D 6．Null represents a value for an attribute in a tuple that is .
A．zero B．an empty set C．a space D．currently unknown or not applicable
B 7. If F is a foreign key of relation R that targets a primary key K of relation S, then F can only take .
A．null values B．null values or values that appear in K
C．values that appear in K D．values that appear in R

B. Calculation of relational algebra (5 points each, total 25 points).
Suppose we are given relation R and S as below, please give results of the following relational algebra expressions.

1. R∩S
   A B C
   3 5 7

2. RS
   R.A R.B R.C S.A S.B S.C
   2 4 6 2 5 9
   3 5 7 3 5 7
   4 6 8 4 5 8

3. A,C®∩A,C(S)
   A C
   3 7
   4 8

4. B,C®∪B,C(S)
   B C
   4 6
   5 7
   6 8
   5 8
   5 9

5. B=5 (R×S)
   R.A R.B R.C S.A S.B S.C
   3 5 7 3 5 7
   3 5 7 4 5 8
   3 5 7 2 5 9

   C. The following schema forms part of a database held in a relational DBMS.
   House(registerNo, Address, Area, Rooms, Floor, Furniture, monthlyrent)
   Customer(C-SSN, Namce, Tel, Sex, profession, birthdate), SSN is the Social Security Number of each customer, 即客户的社会保障号。
   Employee(E-SSN, Name, Sex, speciality, birthdate,DeptNumber)，SSN is the Social Security Number of each employee, 即员工的社会保障号。
   Department(DNumber, Name, office, telephone, description, manager), here ‘manager’ is the SSN of manager
   Rent(registerNo, C-SSN, E-SSN, Date-out, Date-due, Total-rent,)
   FK1: C-SSN references Customer(C-SSN)
   FK2: E-SSN references Employee(E-SSN)
   Inspection(E-SSN,registerNo,Date, Detail)
   E-SSN references Employee(E-SSN)

Identify all the foreign keys for the relation schemas, and the relation to which each foreign key refers. (point: 17)

D. The following tables form part of a database held in a relational DBMS.
student (Sno, Sname, Sdept, DOB, Saddress, Stelephone, Gender, Degree)
lecturer (Tno, Tname, Department, Taddress, Ttel, Title)
course (Cno, Cname, Cpno,Credit)
contact (Tno, Cno, Hours)
enrol (Sno, Cno, Mark)

Formulate the following queries in RA(Relation Algebra) (point: 30):
1)List the numbers of all the students who have enrolled in the course ‘ACSC7103’(the code of course)
ΠSno(Cno=’ACSC7103’(enrol)∞(student))
2）List the numbers, names and Titles of the teachers that teach the course ‘ACSC7103’ .
ΠTno,Tname,Title(Cno=’ACSC7103’(contact)∞(lecture))
3）List the numbers, names, departments of student that have not enrolled the course ‘ACSC7103’.
ΠSno,Sname,Sdept(student)-ΠSno,Sname,Sdept(cno=’ACSC7103’(enrol)∞(student))
4）List all the students who is studying for Doctor Degree.
ΠSno,Sname,Sdept,DOB,Address,Telephone,Gender,Degree(Degree=’Doctor Degree’(Student))
5）List the numbers of the students who has enrolled in all the courses that the student ‘John Smith’ enrolled in.
ΠSno(student)÷ΠSno(Tname=’Peter Smith’(lecture)∞(student))
R(X,Y) ÷ S(Y)==W(X)

ΠSno,cno(enrol)÷Πcno(sname=’John Smith’(student)∞enrol)
每个学号的象集：这个学生所选的课程的集合
E

Assignment of Module02

3.1 Discuss each of the following concepts in the context of the relational data model:
(a) relation
A relation is a uniquely named two-dimensional table with identically structured rows (technically tuples) and distinctly named columns (or attributes).
(b) domain
Set of allowablevalues for one or more attributes.
© attribute
An attribute is a named column of a relation.
(d) tuple
A record of a relation or a row of a relation.
(e) degree and cardinality
The degree of a relation or a row of relation.
The cardinality of a relation is the number of tuples it contains.

3.4 Discuss the properties of a relation
The relation has a name that is distinct from all other relation names in the relational schema;
Each cell of the relation contains exactly one atomic (single) value.
Each attribute has a distinct name.
The value of an attribute are all from the same domain
Each tuple is distinct there are no dulicate tuples.
The order of attributes has no significance
The order of tuples has no significance,theoretically.(however,in practice,the order may affect the effciency ofaccessing tuples.)

3.5 Discuss the difference between the candidate keys and the primary key of a relation. Example what is meant by a foreign key. How do foreign keys of relations relate to primary key? Give examples to illustrate your answer.
(1)Candidate Key
Superkey(K) such that no proper subset is a superkey within the table in each record,values of K uniquely identify that record(uniqueness).No proper subset of K has uniqueness property(irreducibility).
(2)Primary Key
Candidate key selected to identify records uniquely within table.
(3)Foreign Keys
Column,or set of columns ,within one table that matches primary key of some (possibly same) table.

Example:
course
C Cno
001
002 001
003 002
004 003
005 004
Primary key:C
Foreign key:Cno
Constraint Pk_course primary key©
Constraint FK_course foreign key(Cno) references course©
3.6 Define the two principal integrity rules for the relational model. Discuss why it is desirable to enforce these rules.
Entity Intergrity:In a base table ,no column of a primary key can be null.
Referential Integrity:If FK exists in a table,either FK value must match a candidate key value of some record in home table or FK value must be wholly null.

3.7 What is view? Discuss the difference between a view and a base relation.

VierwLThe 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 necessarily exist in the database but can be produced upon request by a particular user,at the time of request.
Base relation:A named relation corresponding to an entity in the conceptual schema,whose tuples are physically stored in the database.
A virtual relation that does not necessarily actually exist in the database but is produced upon request,at time of request.
Contents of a view are defined as a query on one or more base relations
Views are dynamic,meaning that changes made to base relations that affect view attributes are immediately reflected in the view.

Exercises :

The following tables form part of a database held in a relational DBMS:
Hotel (hoteNo, hoteName, city)
Room (roomNo, hoteNo, type, price)
Booking (bookID, hoteNo, roomNo, guestNo, dateFrom, dataTo)
Guest (guestNo, guestName, guestAddress)
Where Hotel contains hotel details and hotelNo is the primary keys;
Room contains room details for each hotel and (roomNo, hoteNo) forms the primary key;
Booking contains details of bookings and bookID is the primary key;
Guest contains guest details and guestNo is the primary key.

3.8 Identify the foreign keys in this schema. Explain how the entity and referential integrity rules apply to these relations.

Enitity Integrity : a rule is designed to assure that every relation has a primary key, and that the data values for that primary key are all valid. Every primary key’s attributes is NON-NULL.
Referential Integrity:In the relational data model, association between tables are defined through the use of foreifn keys. Association between HOTELS&ROOM table is defined by including Hotle_No attribute as a foreign key in ROOM. Referential Integrity constraint is a rule that maintains consistency among the rows of two relations. The rules state that if there is a foreing key in one relation either each foreign key value must match a primary key value in another relation or the foreign key value must be NULL

4.8 describe the relations that would be produced by the following relational algebra operations:
a) ∏hotelNo( price>50(Hotel))
The number of hotel whose room price is more than 50 pounds
b) Hotel.hotelNo=Room.hotelNo(Hotel × Room)
All the hotel’s numbers,names and their room’coding,type and price
c) ∏hotelNo(Hotel∞Hotel.hotelNo=Room.hotelNo（ price>50(Room)）)
All the names of hotels whose romm’s price is above 50 pounds
d) ( dataeTo≥’1-Jan-2002’(Booking))∝Guest
All the numbers,names and addresses of travellers,and the hotel codings,the time of checking in and out and the room’s number of traveller whose reservation is after january 1th,2020.
e) Hotel⊿Hotel.hotelNo=Room.hotelNo( price>50(Room)) (‘⊿’means semi-join operation)
List the hotelNo, hotelName, city, roomNo, type, price of the hotle rooms which price is more than 50
f) (∏guestNo,hotelNo (Booking∞（Guest)）)÷∏hotelNo( city=’London’Hotel))
The coding numbers of hotels that guest in London and the names of guest.
4.12
(a)List full details of all hotels.
Hotel
(b)List all single rooms with a price below $20 per night.
∏roomNo,hotelNo(type=’single’^price<’20’(Room))
©List the names and cities of all guests.
∏guestname,guestaddress(Guest)
(d)List the price and type of all rooms at the Grosvenor Hotel.
∏price,type(hotelName=’Grosvenor Hotel’(Room∞hotel))
(e)List the guests currently staying at the Grosvenor Hotel.
∏(hotelName=’Grosvenor Hotel’(Room∞hotel∞guest))
(f)List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied
∏（ hotelName=’Grosvernor Hotel’(Room∞Hotel))∞(∏guestname( gotelname=’’Grosvernor ’ (Room∞Hotel∞Guest)))
(g)List the guest details(guestNo, guestName, and guestAddress) of all guests staying at Grosvenor Hotel.
∏ guestNo, guestName, guestAddress( hotelname=Grosvernor Hotel’(Room∞Hotel∞Guest))

4.13 Using reational algebra, create a view of all rooms in the Grosvenor Hotel, excluding price detail. What are the advantage of this view?
make the graph more visiual, the information more comprehensive ,reject the various commercial ads, and ensure the security

Test of mudule03

数据库中关系如下：
student (Sno, Sname, Sdept, DOB, Address, Telephone, Gender, Degree)
lecturer (Tno, Tname, Department, Address, Telephone, Title)
course (Cno, Cname, Cpno,Credit)
contact (Tno, Cno, Hours)
enrol (Sno, Cno, Mark)
1.Create the above tables using DDL.(Define the Primary key and foreign keys for each table )

Creat table student
( Sno char(10)
Sname char(30)
Sdept char(30)
DOB char(10)
Address char(100)
Telephone char(20)
Gender char(30)
Degree char(30)
Constraint pk_student primary key(Sno)
)

Creat table lecturer(
Tno char(10)
Tname char(30)
Department char(30)
Address char(30)
Telephone char(20)
Degree char(20)
Constraint pk_lecturer primary key(Tno)
)

Creat table course(
Cno char(10)
Cname char(30)
Cpno char(30)
Credit tinyint
Constraint pk_course primary key(Cno)
)

Creat table contact(
Tno char(10)
Cno char(10)
Hours int
Constraint pk_contact primary key(Tno,Cno)
Constraint fk_contact foreign key(Tno)
References lecturer(Tno)
Constraint fk_contact foreign key(Cno)
References course(Cno)
)

Creat table enrol(
Sno char(10)
Cno char(10)
Mark int
Constraint pk_contact primary key(Sno,Cno)
Constraint fk_contact foreign key(Sno)
References student(Sno)
Constraint fk_contact foreign key(Cno)
References course(Cno)
)

2.Formulate the following queries in RA(关系代数)and SQL（sql语言） respectively:

1)List the number,name and department of all the students who enrolled in the course ‘ACSC7101’(by number)
∏Sno,Sname,Sdept( course=‘ACSC7101’(student∞enrol))

Select Sno Sname Sdept
From student,enrol
Where student.Sno=enrol.Sno
AND enrol.course=‘ACSC7101’

2)List the information(including numbers, names) of the CS department’s students who was born before 1997-01-01.

∏Sno,Sname( Sdept=‘CS’∩DOB<’1997/01/01’(student))
Select Sno Sname
From student
Where Sdept=’CS’
AND DOB<’1997/01/01’

3)List the information(including number, name and department) of the students who are from Illinois and California .

∏Sno,Sname,Sdept( Address=‘California’∪Address=‘Illinois’(student))

Select Sno Sname Sdept
From student
Where Address=‘California’
OR Address=‘Illinois’

4)List the numbers of lectures who doesn’t teach any course.
∏Tno( Cno=null (contact))

Select Tno
From contact
Where Cno IS null