Logical views of data the user a logical view

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: s by Zip Zip Name Zip Name 77022 Grimes 77024 Decker . . . . . . DBMS User B Logical View Credit Hours Credit Name Hrs Hrs Able 37 Decker 45 . . . . . . Translates logical views into Translates data retrieval requests data Translates DBMS requests Translates into physical data retrieval instructions instructions Operating System System Database Database Schema Describe _____________________________ The three levels of schemata are: The next slide depicts these schema graphically Levels of Schema Levels Subschema User A Students by Zip Subschema User B Total Hours Subschema User C Class Roll External Level External User logical views of User portions of the database (subschema) (subschema) Mapping Courses Students Prerequisites Class Enrollment Conceptual Level Conceptual view Conceptual (Enterprise view) of entire database entire Mapping 11 10 11 00 10 00 01 Internal Level Physical view Physical (data storage details) details) Accountants and Schema Accountants most often are involved in which two schema Accountants levels? levels? External subschema should be designed to: Conceptual level schema should be designed to: Data Dictionary Data Contains information about the structure of the database For each data element stored in the database, there is a For corresponding record describing it corresponding Example - vendor data from an Access database: DBMS Languages Data Definition Language (DDL) Data Manipulation Language (DML) Data Query Language (DQL) Report writer DDL Example DDL DDL command to create (not fill) the customer table below: CREATE TABLE (Customer# Name Address City State Zipcode Balance Customer Table Customer # Name ► 0 0 1►► Record ◄ ◄ Contact 0 ►* Customer INTEGER (5), NOT NULL CHARACTER (15), CHARACTER (20), CHARACTER (12), CHARACTER (2), CHARACTER (10), FLOATING (10) ) X Address 0 City 0 State 0 Zipcode 0 Balance 0 of 1 DML must be used to fill the table (next slide) DML Example DML DML command to insert a new row: INSERT INTO Customer (Customer#, Name, Contact, Address, Customer City, State, Zipcode, balance) City, VALUES (2215, ‘Creepy Critters’,’Weird Al’,’7301Crazy LN’, Crawly’,’TX’,72010’,0) Customer Table Customer # Name Contact ► 2215 Creepy Critters Weird Al 1 ► ► ►* of 1 Record ◄ ◄ X Address City 7301 Crazy Ln Crawly State TX Zipcode 72010 Balance 0 DML Example DML DML command to update Creepy Critters: UPDATE SET WHERE Customer Balance = 8476 Customer# = 2215 X Customer Table Customer # Name Contact ► 2215 Creepy Critters Weird Al ◄◄ 1 ► ► ►* of 1 Record Address City 7301 Crazy Ln Crawly State TX Zipcode 72010 Balance 8476 DML command to delete Creepy Critters: DELETE FROM WHERE Customer Customer# = 2215 Access Query Example Access Tables: Order ID 122 238 753 824 829 923 942 Customer Employee Number ID 103 2465 105 1538 103 2465 101 2768 103 1245 104 1285 103 5345 Quantity Unit Price 21 4.52 87 4.49 9 7.85 48 6.48 842 2...
View Full Document

Ask a homework question - tutors are online