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

浙江财经学院期中考试试卷专业、班级： 学号： 姓名：密 封 线浙江财经学院期中考试试卷数据库原理及应用（英）课程试卷考核方式： 闭 卷 考试日期：2012年11月21日适用专业、班级：10电子商务题 号一二三四五六七八九十总分得 分评卷人（共 7 大题）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 ? CAcreating some views in a databaseBmodifying table structures in a databaseCfinding some rows from a tableDcreating some tables in a database(2) In relational model, entity integrity means that B .Athere must not be empty rows in every relationBno attribute of a primary key can be nullCattributes are allowed to be nullDforeign keys are allowed to be null(3) Which of the following operations is not supported by DML ? DAinserting some rows into a tableBdeleting some rows from a tableCfinding some rows from a tableDcreating 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 .Anull valuesBnull values or values that equal to one of the values of KCvalues that appear in KDvalues 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? CAProjection BJoinCSelectionDDivision(6) A relation schema _C_.Acan have exactly one candidate key Bcan have many primary keysCcan have one or many candidate keys Dcan 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.AADD TABLE S(CN CHAR(8) BADD TABLE S ALTER(CN CHAR(8)CALTER TABLE S ADD CN CHAR(8) DALTER TABLE S (ADD CN CHAR(8)(8) For a relation, each candidate key C .Acan only have at least two attributesBcan only have at most one attributesCmay have one or more attributesDmust 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 R1R2R3 will be _A_.AK1 K2 K3BK1+ K2+ K3C(K1+ K2)K3D(K1+ K2)K3(10）In SQL, views are corresponding to the _A_ schema of a database.AexternalBconceptualCinnerDstorage2. 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.Supp_NumberSCitySupplierSnameTelephonecolorcostpriceproductsP_numberPnameTypeStock-quantityCust_NumberDOBAdrresscustomerCnameTelephoneEmailPostcodestatusorderOrd_numberDate_dueTotal_priceDate_orderedproductorderscustomerdetailsPlace_orderquantityprice1nsuppliersupplydatequantitypricenmmn3. 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 customers in shanghai.dAdress=shanghai(Customer)Select *From CustomerWhere Address like shanghai%(2) List all the orders that are outstanding, include order_number, date, Date-due, customers name, address, product and status, order by date.Ord_number,Date,Date-due,Fname,Lname,Address,Name,status (dOrd_status=Outstanding(OrderDetailsCustomerProduct)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=OutstandingOrder by Date(3) List all fresh milk products by names , prices and produce time.Name,price,produce_time (dNames=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(CustomerOrders)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 the customer named John.P_number,Name,Quantity (dLname=John(OrderDetailsCustomerProduct)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_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 th