DBM & INFO RET SYS
DBM & INFO RET SYS INFO 340
Popular in Course
Popular in Informatics
This 15 page Class Notes was uploaded by Brennan Schmeler on Wednesday September 9, 2015. The Class Notes belongs to INFO 340 at University of Washington taught by Staff in Fall. Since its upload, it has received 16 views. For similar materials see /class/192221/info-340-university-of-washington in Informatics at University of Washington.
Reviews for DBM & INFO RET SYS
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 09/09/15
Sample Midterm and Final questions for INFO 340 Database Material Weeks 1 5 1 N E 4 V39 0 gt1 3 points Using a gure explain the lost update problem What feature of databases is used to prevent lost updates 2 points a Who invented relational databases and in what year were they invented b What central beautiful idea underlies them 6 points Explain the differences between conceptual logical and physical database design 2 points Using a diagram and an explanation explain what an outer join is 3 points Write a relational algebra expression for this query Get supplier names for suppliers who do not supply paIt id p001 6 points An investment bank brokers a deal between a buyer and a seller of a publicly traded company Behind the scenes of each of these players are lawyers accountants and negotiators Draw an EER diagram that represents this structure Please include a brief caption that outlines your assumptions 4 points Give a formal definition of a relational database schema Information Retrieval Material Week 6 10 8 9 6 points IR is often represented as a communication process Using a diagram describe this process including the major phases and entities that make it up 2 points Explain the difference between open and closed vocabularies 10 2 points What is a document proxy Why are document proxies used 11 2 points Define recall enhancing and describe one example technique 12 2 points Why do IR systems make use of an inverted file 13 4 points Draw a diagram showing the structure and elements of an inverted file 14 6 points Describe a basic algorithm for indexing a corpus of documents 15 6 points Suppose a high school teacher wants to detect plagiarism in her students work Describe an IR system that might be designed to solve this problem and list 3 IR technical issues that would have to be addressed Problem domain Entities attributes relationships visualizations user needs client m chines HISTORY PLACES INFO340 Data base Relational Model Tables SQL domains attributes keys relational integrity constraints Management amp Information indexes data sys em catalog Remevql Database Implementation Access MySQL PostgresSQL David Hendry Client Tools Server TOOls Class L3905 Psql command shell Linuxi HTML JSPi web browser lNFOr36D Class 6 quotIe Information School of tne University of Washington 6 quotIe Mormation School of tne University of Washington Two Types of SQL Commands I Data Definition Commands Create data base structure CREATE TABLE ALTERTABLE DROP TABLE I Data Manipulation Populate and query tables SEL CT lNFOr36D Class a quotIe Information School of tne Unweity of Washington 6 quotIe Information School of tne Unweity of Washington Database Design Example SQL Versions SQL is an international standard BUT vendors tend to add different features to Database BoatCIub Three tables the languages The textbook contains the ISO syntax but PostgreSQL provides different features in addition to the ISO syntax Check the documentation Sailor sid integer sname string rating integer age real Boat bid integer bnamestring colonstring Reserve sidinteger39 bidinteger39 daxmtate MySQL implements only a subset of SQL quotIe Information School of tne Unweity of Washington lNFOr36D Class 5 lNFOr36D Class 6 quotIe Information School of tne Unweity of Washington Dam Tables Basic Form of SQL Query nemem by SELECT DISTINCT selectlist FROM fromlist WHERE qualification iNroram Ciass 39 iNroram Ciass 6 The lnlormolion Sdlool of The University of Washington 6 The lnlormolion Sdlool of The University of Washington Q List full details about the table sailor Q Find the names and ages of all sailors SELECT sname sage FROM Sailor 5 SELECT FROM SAILOR iNroram Ciass iNroram Ciass a The lnlormo on School of me Unwwy of Washington 6 The lnlormo on School of me Unwwy of Washington Q Find all sailors with a rating above 7 Q Find the names and ages of all sailors SELECT sidI ssnameI srating FROM Sailor AS 5 WHERE srating gt 7 SELECT DISTINCT sname sage FROM Sailor s The lnlormo on School of me Unwwy of Washington iNroram Ciass iNroram Ciass e 2 m E e 0 5 e a E 3 i 3 3 2 m 5 u a E 2 5 o 5 Qualification Clause SELECT Statement Boolean combinations AND ORI NOT Comparison operators ltltltgtgtIgt Basic Syntax SELECT DISTINCT selectlist FROM fromlis1 WHERE Qualification SELECT siolI ssnameI srating FROM Sailor AS 5 WHERE srating gt 7 AND sage lt 60 Mean Ciass 1 Mean Ciass 6 The Information Sdlool of me Universin of Washington The Mormorion Sdlool of me Universin of Washington EXERCISE In general what do the selectlist and qualification clauses do ORDER BY SELECT DISTINCT selectlist FROM fromlist WHERE qualification ORDER BY SELEC DISTINCT select in PROJECTION FROM fromIisl lt5 XPRODUCT WHERE Qualification 4 SELECTION worm Ciass 5 Norm Ciass a The Information School of me Unwwy of Washington 6 The Information School of me Unwwy of Washington O Find all sailors with a rating above 7 and sort by name SELECT siolI ssnameI srating FROM Sailor AS 5 WHERE srating gt 7 ORDER BY ssname Joining Tables The Information School of me Unwwy of Washington iNroram Ciass 39 6 The Information School of me Unwwy of Washington Find the names of Sailors who Consuder these Tables have reserved boat 10 select name from sailor s reserve r where rsid sid and rbib 101 wroram Class wroram Class m 6 The Information Sdlool of me Unwemry of Washington 6 The Mormolion Sdlool of me Unwemry of Washington Step I Cartesian Product Sailor X Reservation Step 2 Consider Only SailorSid Reservationsid The Information School of me Unwwy of Washington wroram Class I wroram Class The Information School of me Unwwy of Washington Step 3 Consider Only Reservationbid 10 Step 4 Consider Only select sname 2 m c i g a g 3 E 3 i 3 3 2 In E a E 2 5 w 5 The Information School of me Unwwy of Washington wroram Class wroram Class 6 The Information Sdlool of me Unwwy of Washmgfon Find the names of Sailors who have reserved boat 10 select name PROJECTION from sailor s reserve r XPRODUCT where rsio sio SELECTION and rbib 101 Mean Ciass a The Information School of me Unwwy of Washmgfon Joins in PosigreSQL select from tl wroram Ciass e 2 e E I 0 5 e a E 3 i a a 2 m 5 u a E 2 5 w 5 Joins in PosigreSQL select from tl natural inner join t2 nuln name I value wroram Ciass 6 The Mormoiion Sdlool of me Unwwy of Washmgfon Joins in PosigreSQL select from tl cross join t2 6 The Information School of me Unwwy of Washmgfon 1 a 2 lb 2 1a II xxx T 1 1a l3 Iyyy quot quot39quot a e 1a 5 Izzz i39x 2b 1xxx 5 39m 2 b I Iyyy quot xquot 2 b 5 Izzz wow mam Joms In PosigreSQL T1 select from tl nuln name I value wroram Ciass The Information School of me Unwwy of Washmgfon in Posig reSQL select from tl left join t2 using nun nuln name I value I value xxx M xxx wroram Ciass Q Find the average age of all sailors with a rating of 10 Aggregate Operators COUNT DISTINCT A SUM DISTINCT A SELECT AVGsage AVG DISTINCT A FROM Sailor s MAXA WHERE sqge gt 10 MNA wroram Class wroram Class n 6 The Information School of me Unwemry of Washington 6 The lnlormolion School of me Unwemry of Washington GROUP BY and HAVING Q How many sailors are there clauses Aggregate operators apply to all qualifying SELECT COUNT rows of a relation FROM Sailor Sometimes it is necessary to apply an aggregate operator to a number of groups of rows in a relation 6 The Information School of me Unwwy of Washington wroram Class n wroram Class a The Information School of me Unwwy of Washington GROUP BY and HAVING clauses SELECT DISTINCT selectlist FROM fromlist WHERE qualification GROUP BY groupinglist HAVING group qualification Q Find the age of the youngest sailor for each rating level SELECT MNsage FROM Sailor S WHERE srating J and J 12 10 This is tedious there is a better way The Information School of me Unwwy of Washington wroram Class 2 wroram Class 6 The Information School of me Unwwy of Washington 6 The lnlormorion School of The University of Washington Q Find the age of the youngest sailor for each rating level SELECT srating MNsage FROM Sailor s GROUP by srating wroram Class I a The lnlormo on School of me Unwwy of Washington Su bqueries select statements can be embedded within select statements Three types of subquery Scalar Row subquery Table subquery See Text for details wroram Class I e 2 m E I 0 a e a E 3 i a e 2 m 5 u a E 2 5 w 5 Example List all sailor ids who have reserved the blue boat NOTE Assume that there is only one blue boat select sid from reservations where bid select bib from boat where color blue wroram Class 6 The lnlormorion School of The University of Washington Q Find the age of the youngest sailor who is eligible gt 18 years old for each rating level with at least two such sailors SELECT srating minsage FROM Sailor 5 WHERE sage gt18 GROUP BY srating HAVING COUNTgt1 wroram Class I 6 The lnlormo on School of me Unwwy of Washington Database Update Operations These commands do what you might expect INSERT UPDATE DELETE Read about them in Chapter 5 Mean Class 1 The lnlormo on School of me Unwwy of Washington Summary SQL is a language for updating and query relations It is extremely powerful but can be tricky to use Different vendor provide extensions and partial im plem entations consult the documentation wroram Class 1 6 Tile Information sdtool of the University of Washington A Word on SQL Syntax I I expect that you will be familiar with the basic structure of SQL syntactic details will not be graded on the midterm and ina But more important is to develop an understanding for the structure of different kinds of queries B Ing Sub queries Cartesian product natural ioin outer ioin Aggregate functions Etc wroram Ciass quotIe Morma on sdtool of the University of Washington Next Time Introduction to data definition Indexes and storage Views Transactions wroram Ciass The lnlormoiion School of the Unwemry of WashIngfon INFO340 Database Management amp Information Retrieval David Henolry Class LO The lnlormoiion School of the Unwemry of WashIngfon Topics Introduction to SQL Data Manipulation Views Transactions Indexes INFOVSOEI CIassB The lnlormo on School of the Unwrst of WashIngfon SQL Data Definition Language The lnlormo on School of the Unwrst of WashIngfon Maior Statements Create Domain Create Table Create InoIex Create View And a few others see chapter 6 INFOVSOEI CIassB The lnlormo on School of the Unwrst of WashIngfon Create Domain Constraints The sailor s rating must be between 17 The sailor s name must be fewer than 30 characters The color of a boat must be one of white reoII blue yellow black INFOVSOEI CIassB The lnlormo on School of the Unwrst of WashIngfon Specified in SQL CREATE DOMAIN Sailorch As VARCHAR5 39 CREATE DOMAIN SuilorRuIing AS INTEGER CHECK VALUE BETWEEN I and 7 CREATE DOMAIN SailorName As VARCHAR3 CREATE DOMAIN BouIColor As CHARM CHECK VALUE IN W R G 53 INFOVSOEI CIassB The Information School of the University of Washington Create Table CREATE TABLE sailor sid Sailorld NOT NULL name SailorName rating SailorRating age integer PRIMARY KEY sid Moran ClassE The Mormoiion School of the University of Washington Create Table I CREATE TABLE Reservation sid Sailorld NOT NULL i Boatld NOT NULL rdate DATE PRIMARY KEYsidbid FOREIGN KEY sid REFERENCES Sailor ON DELETE NO ACTION ON UPDATE CASCADE FOREIGH KEY bid REFERENCES Boat ON DELETE NO ACTION ON UPDATE CASCADE Moran ClassE The Information School of the Unwwy of Washington Views The Information School of the Unwwy of Washington Views Defined A view is a virtual relation that does not necessarily exist in the database but is available when requested A view is defined as a query against one or more base tables or views Moran ClassE 1 The Information School of the Unwwy of Washington Example CREATE VIEW simple AS SELECT n a t2b t3c FROM tl t2 t3 complex blah SELECT a b c FROM simple WHERE q blqh Moran ClassE The Information School of the Unwwy of Washington Two Implementation Approaches View resolution Run view request against source tables then perform the reques View materialization Store the view in a temporary table Complication Nee to preserve currency of view as underlying tables are updated Moran ClassE I The lnlormolion Sdlool of the University of Washington Why Use Views Data independence Improved security Reduce com plexity Disadvantages Update restrictions best for querying Performance Nice summary on p 183 wroraln ClassE 1 The lnlormolion Sdlool of the University of Washington Transactions The lnlormo on School of me Unwwy of Washington Transaction defined An action or series of actions carried out by a single user or application program which reads or updates the contents of a database Extremely important concept Will examine transactions a little in lab wroraln ClassE 5 The lnlormo on School of me Unwwy of Washington Properties of Transactions Atomicity All or nothing It works fully or nothing happens Consistency The db remains in a consistent state Isolation Transactions are independent of each other Durability Transactions are permanent and won t be lost because of a subsequent failure wroraln ClassE The lnlormo on School of me Unwwy of Washington Key Idea SQL provides operations for controlling transactions Commit Rollback Extremely important in systems with concurrent readwriteupdate operations Chapter 19 in text for more wroraln ClassE 39 The lnlormo on School of me Unwwy of Washington File Organization and Storage Structures The lnlormorion School of the University of Washington Mapping Conceptual Memor Hierarch Abstractlons to the Physlcal y y Data is stored on nonvolatile media but to answer a query data must be brought into main memory Time to access a data item Main memory 50 nanoseconds 10 399 sec Disk 20 microseconds 10 395 sec The lnlormorion School of the University of Washington wroram Class wroram ClassE The lnlormo on School of the Unwwy of Washington soL QUERIES Goal Mlnlmlze Dlsk Accesses Higher Level Code E E e E 6 Ratio 20103965010399 E e 400 5 E Buffer Pool 6 3 Page N 100 1000 times faster to access memory g Mam Mammy u compared to disk 39g Page quot19quot E Page t Thus it is important to reduce disk accesses Lg Sk a lNFOrC MU ClassB I lNFOrC MU ClassB The lnlormo on School of the Unwwy of Washington File Organization A FILE on disk The physical arrangement of data on records and pages on secondary storage disk Types El Heap unordered Sequential ordered Hash files Q Which is the best The lnlormo on School of the Unwwy of Washington wroram Class e wroram ClassE The lnlormaiion School of the University of Washington A It Depends on the Nature of the Operations The standard operations Scan Fetch all records in a file Search with equality selection ere age 10 AND rating 3 Search with range selection where age gt 10 Insert a record Delete a record wroeaon ClassE e The lnlormaiion School of the University of Washington FSA N Steps for InsertDelete Identify page Fetch page from disk Modify page to include new record Write page back to the disk Depending file organization this may have to be repeated many times wroeaon ClassE Ii The lnlormation School of the Unwstty of Washington Heap Files No order to the records Operations Search is very slow linear Insert is very fast Delete Search wroeaon ClassE The lnlormation School of the Unwstty of Washington Ordered File Records are ordered by an ordering key eg sail boat id Operations Equality search is fast binary search Range searching is fast binary search Insertdelete is very slow wroeaon ClassE H The lnlormation School of the Unwstty of Washington Hash Files I The address of a record is calculated from one or more fields I Operations Equality search is very fast Range search is very slow Insert is very fas1 Delete is very fast BUT depends on the hash functionnature of keys wroeaon ClassE The lnlormation School of the Unwstty of Washington Summary Files on disks contain physical records note physical records table rows Files are spread over many pages Pages are the unit of transfer between disk and memory Pages are fixed size 4K or 8K Files have different organizing schemes for placing records on 39 Hasning wroeaon ClassE 1 quotIe Information Sdlool of me Unwemry of Washmgron Nexi Time Introduction to EntityRelationship Modeling Nroram C ass
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'