数据库期中试卷(附答案)
《数据库原理及应用（英）》课程试卷
考核方式：闭卷考试日期：2012年11月21日
适用专业、班级：10电子商务
1.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.（Points: 20 ）
(1) Which of the following operations is not supported by DDL ? C
A．creating some views in a database B．modifying table structures in a database
C．finding some rows from a table D．creating some tables in a database
(2) In relational model, entity integrity means that B.
A．there must not be empty rows in every relation
B．no attribute of a primary key can be null
C．attributes are allowed to be null
D．foreign keys are allowed to be null
(3) Which of the following operations is not supported by DML ?D
A．inserting some rows into a table B．deleting some rows from a table
C．finding some rows from a table D．creating some tables in a database
(4) If F is a foreign key of relation R that references the primary key K of relation S, then F can only
take B.
A．null values B．null values or values that equal to one of the values of K
C．values that appear in K D．values that appear in R
(5) Which of the following operations can retrieve from a relation all records that satisfy a condition
to form a new relation? C
A．Projection B．Join C．Selection D．Division
(6) A relation schema __C____.
A．can have exactly one candidate key B．can have many primary keys
C．can have one or many candidate keys D．can have one or many primary keys
(7) In SQL, in order to add a new column CN into base table S, we use C statement.
A．ADD TABLE S(CN CHAR(8)) B．ADD TABLE S ALTER(CN CHAR(8)) C．ALTER TABLE S ADD CN CHAR(8) D．ALTER TABLE S (ADD CN CHAR(8))
(8) For a relation, each candidate key C.
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 (9）Suppose Relation R1, R2 and R3 have K1, K2 and K3 tuples , respectively, then the number of tuples in R1′R2′R3 will be __A____.
A．K1′ K2′ K3B．K1+ K2+ K3C．(K1+ K2)′K3D．(K1+ K2)÷K3 (10）In SQL, views are corresponding to the __A___ schema of a database.
A．external B．conceptual C．inner D．storage
2.Given the following descriptions, create an appropriate ER diagram for the
management of the Library. (point:20)
They have a number of suppliers that supply the products for sale. Each supplier has a unique supplier number, and the name, city and telephone number. A supplier can supply several kinds of products and one kind of products may be supplied by several suppliers. The supply information includes supply date, price and supply quantity.
They have many kinds of products that they provide to their customers. Each product has a unique product number, and the name, type, color, quantity, together with a cost and a price.
They have a number of customers. Each customer is given a unique customer number. Each customer has a name, customer addresses, DOB(date of birth), gender(性别)，postcode, the customer telephone number and Email.
A customer may place zero, one or more orders at a time, and an order is always placed by
one customer alone. Each order has a unique order number, the date ordered, the date due, the total price, and the status.
An order may include one or more than one products, and a product may be included in more than one order. For each product being ordered in an order, its quantity, price are recorded.
3.The following tables form part of a database held in a relational DBMS.
Product(P_number, Name, type, cost, price, description, produce_time, useful-life) Customer(Cust_Number, Fname, Lname, Address, profession, birthday, sex)
Order(Ord_number, Cust_Number, date, Date_due, Total_price, Ord-status) Details(Ord_number, P_number, quantity,status)
Where Product contains product details and P_number is the primary key. Customer contains customer details for each hotel. Each customer can play one or more orders and each order is played by one customer. Each order includes one or more products. The statue of order denotes whether the order is completed, valued as ‘unaffirmed’(未确认)，‘affirmed’（已确认），‘outstanding’（已付款待送货）, ‘delivered’（已送货）and ‘invoice’（已开发票）.
(a) Formulate the following queries in RA and SQL respectively: (point:30)
(1) List full details of all customer s in ‘shanghai’.
Adress=’shanghai’(Customer)
Select *
From Customer
Where Address like ‘shanghai%’
(2) List all the orders that are ‘outstanding’, include order_number, date, Date-due, customer’s name, address, product and status, order by date.
∏Ord_number,Date,Date-due,Fname,Lname,Address,Name,status(Ord_status=’Outstanding’(Order∞Details∞Customer ∞Product))
Select Ord_number,Date,Date-due,Fname,Lname,Address,Name,status
From ((Order O join Details D on O.Ord_number=D.Ord_number) join Customer
C on O. Cust_Number=C. Cust_Number) join Product P on D. P_number=P. P_number
Where Ord_status=’Outstanding’
Order by Date
(3) List all fresh milk products by names , prices and produce time.
∏Name,price,produce_time (Names=’fresh milk’(Product)
Select Name,price,produce_time
From Product
Where Names=’fresh milk’
(4) List the information of customers who have never played an order.
∏Cust_Number, , Fname, Lname(Customer)- ∏Cust_Number, , Fname, Lname(Customer∞Orders)
Select Cust_Number, Fname, Lname
from customer
where Cust_Number not in
(select Cust_Number,
from Orders)
(5) List the numbers, names and quantities of products that bought by the customer
named ‘John’.
∏P_number,,Name,Quantity (Lname=’John’(Order∞Details∞Customer∞Product))
Select P_number,,Name,Quantity
From ((Order O join Details D on O.Ord_number=D.Ord_number) join Customer C on O. Cust_Number=C. Cust_Number) join Product P on D. P_number=P.
P_number
Where Lname=’John’
(b) Formulate the following questions in SQL: (point:30)
(5) How many products of each type?
Select count(*)
From Product
Group by type
(6) What is the total revenue today?
Select sum(Total_price)
From Order
Where Date=getdate()
(7) How many kind of products that have never purchased by customers?
Select count(*)
From Product
Where P_number NOT in
( Select P_number
From Details)
(8) Move all the orders and their details with a date before 2010-12-31 from Order and Details to their archive tables called Order_archive and Detail_archive, the archive table have same structure.(First insert the information into two new tables ,and then delete from) (12points)
Insert into Order_archive(Ord_number, Cust_Number, date, Date_due, Total_price, Ord-status)
Select Ord_number, Cust_Number, date, Date_due, Total_price, Ord-status
From Order
Where Date<=’2010-12-31’
Insert into Details_archive(Ord_number, P_number, quantity,status)
Select Ord_number, P_number, quantity,status
From Details
Where Ord_number in
(select Ordnumber
From Order
Where Date<=’2010-12-31’ )
Delete from Details
Where Ord_number in
(select Ordnumber
From Order
Where Date<=’2010-12-31’ )
Delete from Order Where Date<=’2010-12-31’
(9) Create a view of all information of products, including product names, types, prices, colors.
Create view Porduct_infor(Name ,type, price, color)
As
Select Name ,types, prices, color
From Product
(10) List the customers who have played orders more than 20 times by numbers and
names.
Select Cust_Number, Fname, Lname
From customer
Where Cust_Number in
(select Cust_Number
From Order
Group by Cust_Number
Having count(*)>20
)
(11) List the detailed information of orders that were played in October and have not
yet been delivered.
Select *
From Orders
Where status=’outstanding’and date between ‘2012-10-01’and ‘2012-10-31’