数据库理论作业.doc

Assignment Cover SheetStudents are requested to complete all details on both portions prior to submission at the to enable a speedy service. Please use a ballpoint pen.STUDENT ID: 2009016(Please enter your Registration number here)MODULE CODE:COM147J4MODULE TITLE:Introduction to DatabaseLECTURERS NAME:Lu ShaoMingASSIGNMENT NO: 1 OF 3 CONTRIBUTION TOWARDS:Coursework Total 30 %DATE SET:23 April, 2010APPROXIMATE TIME TO BE SPENT:1 WeeksHand-In Date:30 April,2010ON RETURNTUTORS COMMENTS ON WORK: Tutors Signature: GRADE/MARKSTUDENT PLEASE NOTEIt is your responsibility to ensure that ALL course work is submitted on time to the specified location. You must keep copies of all work submitted. You are advised to keep your receipt safe it is required in the event of any query concerning this Assignment.ASSIGNMENT RECEIPT Not valid unless stampedStudents Signature:Date:It is your responsibility to retain this receipt. StampAssignment 1 (of 3)Date of Issue:9:00 AM, 23 April, 2010Submission Date:11:00 AM, 30 April, 2010Feedback: 11:00 AM, 30 April, 2010 Assignment Information This assignment contributes 30% of the overall coursework mark for the module, the other 60% being awarded for a class test. Coursework contributes 25% of the final module result, the other 75% being an end of semester examination. Students should attempt and submit solutions to all four of the questions below. Each question carries equal marks.Question 1 Translate each of the following terms:(a)relation (b)database (c)Database management system(d)candidate key (e)data model (f)NULL(g)domain (h)relational algebra(a) Relation:A relation is a table with columns and rows.(b) database:A shared collection of logically related data,and a description of this data,designed to meet the information needs of an organization.(c) Database management system:(DBMS)A software system that enables users to define,create,maintain,and control access to the database.(d) Candidate key:A superkey such that no proper subset is a superkey within the relation.(e) Data model:An integrated collection of concepts for describing and manipulating data,relationships between data,and constraints on the data in an organization.(f) NULL:Represents a value for an attribute that is currently unknown or is not applicable for this tuple.(g) Domain:A domain is the set of allowable valus for one or more attributes.(h) Relational algebra:The relational algebra is a theoretical language with operations that work on one or more relations to define another relation without changing the original relation(s).Question 2 Describe the approach taken to the handling of data in the early file-based systems. List the disadvantages of this approach.In the early,the file-based systems is a collection of application programs that perform services for the end-users such as the production reports.Each program defines and manages its own data.The disadvantages of this approach:(1) Separation and isolation of data(2) Data depandence(3) Incomtible file formats(4) Fixed queries/proliferation of application programsQuestion 3 Explain the function of each of the clauses in the SELECT statement.what restrictions are imposed on these clauses.FROM specifies the table or to be usedWHERE filters the rows subject to some conditionGROUP BY forms groups of rows with the same culumn valueHAVING filters the groups subject to some conditionSELECT specifies which columns are to appear in the outputORDER BY specifies the order of the outputFor the following exercise, use the Hotel schema defined at the start of the Exercise at the end of Chapter 3 in textbook.Question 4 Generate SQL commands for the following queries:(1) List all the rooms.(2) List all single roos with a price below 20 per night(3) List the price and type of all rooms at the Grosvenor Hotel. (1) soulution: statement:SELECT room.*FROM room;1q,(2) s