Week 3 Tutorial - Pravin Shetty > Resume
Contents
1. Week 3 Tutorial Relational Database Model Additional Questions 1 Describe in your own words the five integrity constraints specified in the Relational Model Version 2 2 List the Primary and Foreign key attribute s for each table in the following database n p hr MA n E PRL A b f gt 1i 2 m Tene an ar kr js LES PP ie pi i m ay Ee LT tes fi ciate a Hoel on li nee 23 Sep 03 AA 32212212W11 SSS GSS GE eed PEE E ee SIGE i af i LI r r p z lsa am 15 Jul 00 AD 456 00845 R45 17 Oct 01 AA 341 96573 264 dante ta aint 3 Which attributes in the database above enforce Entity integrity 4 Which attributes in the database above enforce Referential integrity 5 Which attribute s in the database above could enforce column integrity 6 Why is there not an integrity violation in the TRUCK table for truck 1004 7 In the above database what would happen if I deleted base 501 and the delete rule between the BASE and TRUCK tables was 1 DELETE RESTRICT 11 DELETE CASCADE 2 of 7 Week 3 Tutorial Relational Database Model 11 DELETE SET NULL iv DELETE SET DEFAULT default value is 99 8 In the above database what would happen if I updated base 501 and the update rule between the BASE and TRUCK tables was 1 UPDATE RESTRICT 11 UPDATE CASCADE 11 UPDATE SET NULL iv UPDATE SET DEFAULT default value is 99 9 When a new row is inserte2. Week 3 Tutorial Relational Database Model Tutorial 3 The Relational Database Model References Topic 3 Lecture notes Rob P amp Coronel C Database Systems Design Implementation amp Management 6th Edition 2004 Chapter 3 Review Questions 1 5 9 amp 11 Rob P amp Coronel C Database Systems Design Implementation amp Management 7th Edition 2006 Chapter 3 Review Questions 1 5 9 amp 11 Review Questions 1 What is the difference between a database and a table 2 What is entity integrity and referential integrity 3 Why are entity integrity and referential integrity important in a database 4 A database user manual notes that The file contains two hundred records each one of which contains nine fields Use appropriate relational database terminology to translate the preceding statement 5 Use the small database shown in Figure Q3 5 to illustrate the difference between a natural JOIN an equiJOIN and an outer JOIN Database name Ch03 CollegqeQue Table name STUDENT _ STU LCOvE PROF CODE gt 100278 128569 512272 4 531235 2 E 531268 553427 1 9 What are homonyms and synonyms and why should they be avoided in database design 11 Identify and describe the components of the table shown in Figure Q3 11 using correct terminology Use your knowledge of naming conventions to identify the table s probable foreign key s 1 of 7
3. lecture slides To accomplish this you will need to make use of the IRA command REDEFINE lt relname gt lt attribute gt which can be used to rename the attributes of a temporary relation eg redefine answer1 name address city would redefine the attributes in relation answer as name address and city 1 2 IRAWIN Tutorial Task Task 2 Enrichment task If time permits you should attempt this task create an IRA Database Environment DBE called RENTAL consisting of three relations BUILDING building number char 4 building address char 20 building value max value 999999 TENANT tenant number char 4 tenant name char 20 tenant contact address char 20 RENT building number char 4 tenant number char 4 lease period months 99 bond amount max value 999999 Add some sample data to the DBE To complete this exercise you will need to read the IRA online help Help Contents Getting Started Creating anew DBE Using your newDBE write IRA statements to solve the following record the IRA commands used to achieve the desired result 6 of 7 Week 3 Tutorial Relational Database Model 1 List the name and contact address of all tenants 2 List the building number and lease period for all rented buildings 3 List the tenant name building address and bond amount for all tenants A Week 3 tutorial as a PDF file 7 of 7
4. by the separator e The second section of the schema declares the actual relations each declaration begins with a relation name and then has listed the attributes of the relation their selected domain and 1f the attribute is a primary key or not For example customer 3 custno custno P cname person_name N city town_name N declares a relation called customer which is made up of three attributes e custno drawn from the custno domain and the relations primary key e cname drawn form the person_name domain and not part of the relations primary key and 5 of 7 Week 3 Tutorial Relational Database Model e city drawn from the town_name domain and not part of the relations primary key Using the sample DBE and IRA answer the following questions record the IRA commands used to achieve the desired result 1 display all the customers with a customer number gt 000002 2 display the name and city of all customers 3 display the name and city of all customers with a customer number gt 000002 4 do a natural join of customer and ordhead 5 show the customer name order and orderdate for all orders 6 show the order product product description and qty ordered for all orders of customer 000001 7 do a natural join of whouse and stock WITHOUT using join Remember that although many RDBMS vendors implement a join operation a natural join can be created from a combination of several native operations see your
5. d in the TRUCK table what must be checked 10 When the TYPE _CODE attribute is updated for truck 1001 what must be checked 11 When truck 1005 is deleted from the TRUCK table what must be checked 12 Which integrity constraints should never permit violations and which integrity constraints should provide a violation response 13 Give examples of possible violation responses Practical Issues Instructional Relational Algebra Instructional Relational Algebra IRA is an educational tool which allows students to investigate RELATIONAL ALGEBRA commands the base language of the relational database model To make clear the differentiation between IRA and a relational database the environment that IRA operates in is called a DataBase Environment DBE Within a DBE which 1s described and created by a schema relational algebra commands can be tested against the stored data Installing IRA Instructional Relational Algebra is available as ira zip please note this is a Windows application To install this file a copy this file to a temporary directory and extract the contents to an IRA folder In the University labs please extract the files into a folder IRA under the C DATA folder At home I suggest extracting the files to CAIRA b if you are installing at home do a find on your hard disk F3 and look for a file called CTL3DV2 DLL There is a copy in your ira directory and possibly one in your windows or windows system director
6. y If you only have a single copy in your IRA directory this file needs to be copied to your c windows directory If there is already a file of this name somewhere in your windows directory DO NOT overwrite it with the IRA copy c delete the file CTL3DV2 DLL in your IRA directory make sure you do not delete the copy under Windows You should then be able to run IRA by selecting the file irawin in your IRA directory Using IRA 3 of 7 Week 3 Tutorial Relational Database Model The initial screen of IRA looks like Instructional Relational Algebra File Edit DBE Query Help Select Help and then Contents This will present you with the help system in two screens a Table of Contents and the Main Help screen If the two screens are obscuring one another use your mouse to move the top Table of Contents to one side The table of contents presents the help topics as chapters of a book ee Getting Started Ge Menu Options a Command Syntax Go File Types g Editor DOS Utilities As you select and open a chapter the main help screen will alter to reflect where you are in the help table of contents You should thoroughly read through the first three chapters Getting Started Menu Options and Command Syntax If you find that the IRA application help is not sufficient to get you started a movie is available which shows the basic operation of IRA To play this AVI which was created by the TechSmith Camtasia product
7. you will need to install TechSmith s special screen TSCC codec 190K After installing the TSCC codec if you have problems playing the movie directly from your browser I would suggest you right click the appropriate file below depending on your connection speed download the file to a local directory and then use your movie player to view the movie IRA Tutorial Movie e an avi file 10 5 Mb or e MS Windows streaming media format 640K this is of slightly lower quality but has been optimised for dial up modems 1 1 IRAWIN Tutorial Task The schema file for the sample DBE 1s 4 of 7 Week 3 Tutorial Relational Database Model custno 6 person_name 18 town_name 12 prodno 6 prod_desc 11 ordno 6 date 8 quantity 4 wno 4 PM customer 3 custno custno P cname person_name N city town_name N ordhead 3 ordno ordno P custno custno N odate date N product 2 prodno prodno P description prod_desc N whouse 2 wno wno P city town_name N ordline 3 ordno ordno P prodno prodno P qtyord quantity N stock 3 wno wno P prodno prodno P qtyonhand quantity N Explanation of this schema e The first section of this IRA Schema file declares the domains which will be used and the size of each domain all domains are text data type only For example the first line declares a domain called custno which is 6 characters long After all domains have been declared the end of this section of the schema is signalled
Download Pdf Manuals
Related Search