NIIT_MM1习题与解答.doc

NIIT-MMS1 Solutions to the Exercises Volumn3-Introduction to Relational Database Management Systems Chapter 1 Exercise1 Identify the entities,relationships, and attributes in the following ERdiagram. entities: EMPLOYEE, DEPARTMENT attributes of EMPLOYEE:EMP_NO, NAME, ADDRESS attributes of DEPARTMENT:DEPT_NAME, CAPACITY relationship:WORKS Chapter 2 Exercise1 Depict the relationships between the following entities. 1. Mother and a Child bring-up 2. University and Faculty have 3. Airplane and Passengers board Chapter 3 Exercise1 Normalize the following relation. (OrderNo,OrderDate,TotalCost,ToyID,ToyName,FirstName,LastName,Address) 【Solution】 (OrderNo,ToyID,FirstName,LastName,OrderDate,TotalCost) (ToyID,ToyName) (FirstName,LastName,Address) Chapter 3 Exercise2 Normalize the following data structure. 【Solution】 (EMP-CODE,EMP_NAME,ADDRESS) (EMP_CODE,DEPARTMENT_CODE) (DEPARTMENT_CODE,DEPARTMENT_NAME,DEPARTMENT_HEAD) Volumn3-Querying and Managing Data Using SQL Server 2005 Chapter2 Exercise1 Display the details of all the customers. 【Solution】 SELECT * FROM Sales.Customer Chapter2 Exercise2 Display the ID, type, number and expiry year of all the credit carde in the following format. Credit Card ID Credit Card Type Credit Card Number Expiry Year 【Solution】 SELECT Credit Card ID = CreditCardID, Credit Card Type = CardType,Credit Card Number = CardNumber, Expiry Year = ExpYear from Sales.CreditCard Chapter2 Exercise3 Display the customer ID and the account number of all the customer who live in the Territory ID 4. 【Solution】 SELECT CustomerID, AccountNumber FROM Sales.Customer WHERE TerritoryID = 4 Chapter2 Exercise4 Display all the details of the sales orders that have a cost exceeding $2000. 【Solution】 SELECT * FROM Sales.SalesOrderDetail WHERE LineTotal 2000.00 Chapter2 Exercise5 Display the sales order details of the product named ‘Cable Lock’. 【Solution】 SELECT * FROM Sales.SalesOrderDetail WHERE ProductID=843 Chapter2 Exercise6 Display