数据库期中试卷(附答案)

浙江财经学院期中考试试卷《数据库原理及应用（英）》课程试卷考核方式： 闭 卷 考试日期：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 ? CA ．creating some views in a databaseB ．modifying table structures in a databaseC ．finding some rows from a tableD ．creating some tables in a database (2) In relational model, entity integrity means that B .A ．there must not be empty rows in every relationB ．no attribute of a primary key can be nullC ．attributes are allowed to be nullD ．foreign keys are allowed to be null(3) Which of the following operations is not supported by DML ? DA ．inserting some rows into a tableB ．deleting some rows from a tableC ．finding some rows from a tableD ．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 canonly 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 acondition 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 keyB ．can have many primary keysC ．can have one or many candidate keysD ．can have one or many primary keys (7) In SQL, in order to add a new column CN into base table S, we use Cstatement.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 attributesC．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．storage2.Given the following descriptions, create an appropriate ER diagram for themanagement 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 hasa 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 placedby 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 CustomerWhere 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’(Orde r∞Details∞Customer∞Product))Select Ord_number,Date,Date-due,Fname,Lname,Address,Name,statusFrom ((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_numberWhere 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_timeFrom ProductWhere 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, Lnamefrom customerwhere Cust_Number not in(select Cust_Number,from Orders)(5) List the numbers, names and quantities of products that bought by thecustomer named ‘John’.∏P_number,,Name,Quantity ( Lname=’John’(Orde r∞Details∞Customer∞Product)) Select P_number,,Name,QuantityFrom ((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_numberWhere Lname=’John’(b) Formulate the following questions in SQL: (point:30)(5) How many products of each type?Select count(*)From ProductGroup by type(6) What is the total revenue today?Select sum(Total_price)From OrderWhere Date=getdate()(7) How many kind of products that have never purchased by customers?Select count(*)From ProductWhere P_number NOT in( Select P_numberFrom 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-statusFrom OrderWhere Date<=’2010-12-31’Insert into Details_archive(Ord_number, P_number, quantity,status)Select Ord_number, P_number, quantity,statusFrom DetailsWhere Ord_number in(select OrdnumberFrom OrderWhere Date<=’2010-12-31’ )Delete from DetailsWhere Ord_number in(select OrdnumberFrom OrderWhere 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)AsSelect Name ,types, prices, colorFrom Product(10) List the customers who have played orders more than 20 times by numbers andnames.Select Cust_Number, Fname, LnameFrom customerWhere Cust_Number in(select Cust_NumberFrom OrderGroup by Cust_NumberHaving count(*)>20)(11) List the detailed information of orders that were played in October and havenot yet been delivered.Select *From OrdersWhere status=’ outstanding’ and date between ‘2012-10-01’ and ‘2012-10-31’。