ACC 3202 ch13

Schema an employees access to data should be s

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: ema of data that is relevant to the performance of his job. relevant Chapter 13-20 DATABASE SYSTEMS The Chapter 13-21 data dictionary DATABASE SYSTEMS Information Information provided for each element includes: includes: A Chapter 13-22 description or explanation of the element. The records in which it is contained. Its source. The length and type of the field in which it is The stored. stored. The programs in which it is used. The outputs in which it is contained. The authorized users of the element. Other names for the element.(Synonyms & Other Homonyms) Homonyms) The History of Databases The History of Databases Relational Database Model ► Developed in 1969 ► Stores data in two-dimensional tables ► Most widely used database structure today ► Examples include; IBM DB2, Oracle Database, and Microsoft Access® Chapter 13-23 SO 4 The importance of databases and the historical progression from flat-file databases to relational databases DATABASE SYSTEMS DBMS Languages Every Every DBMS must provide a means of performing the three basic functions of: functions Creating a database Changing a database Querying a database Chapter 13-24 DATABASE SYSTEMS Creating a database: The The set of commands used to create the database is known as data definition language (DDL). DDL is used to: (DDL) Build the data dictionary Initialize or create the database Describe the logical views for each individual user or Describe programmer programmer Specify any limitations or constraints on security Specify imposed on database records or fields imposed Chapter 13-25 DATABASE SYSTEMS E.G. of DDL Chapter 13-26 CREATE TABLE employees ( fname VARCHAR(20), lname VARCHAR(20), ssn CHAR(9), bdate DATE, sex CHAR(1), salary DECIMAL(10, 2), dept TINYINT(4), PRIMARY KEY (ssn) ); DATABASE SYSTEMS Changing a database The The set of commands used to change the database is known as data manipulation language (DML). DML is used for maintaining language DML the data including: the Updating data Inserting data Deleting portions of the database Chapter 13-27 DATABASE SYSTEMS Querying a database: The The set of commands used to query the database is known as data query language (DQL). DQL is used to interrogate the database, (DQL) DQL including: including: Retrieving records Sorting records Ordering records Presenting subsets of the database The The DQL usually contains easy-to-use, powerful commands that enable users to satisfy their own information needs. information Chapter 13-28 DATABASE SYSTEMS Querying a database: There are five basic types of query. Select Insert Delete Update Join Chapter 13-29 DATABASE SYSTEMS A selection query might request all employees in Dept #2. Dept An insertion query might add the row An query (Susan, Lane, 654872109, 1959-03-31, F, (Susan, 70000.00, 2). 70000.00, A deletion query might delete the row with SSN = 654872109 An update query might change the department An number from 2 to 5 number Chapter 13-30 DATABASE SYSTEMS SQL format for SELECT SELECT fname, lname, sex, bdate, salary FROM employees WHERE (bdate >= ’1982-01-01’ AND sex = ’M’) WHERE OR (bdate >= ’1976-01-01’ AND sex = ’F’); OR Chapter 13-31 DATABASE SYSTEMS SQL format for INSERT INSERT INTO employees VALUES (’Joe’, ’Plumber’, ’222334444’, ’1967-07-04’, ’M’, 60000.00, 4), 60000.00, (’Jim’, ’Wilson’, ’333445555’, ’1992-02-01’, ’M’, 25000.00, 4),...
View Full Document

This note was uploaded on 03/30/2014 for the course ACC 3202 taught by Professor Baron during the Spring '12 term at CUNY Baruch.

Ask a homework question - tutors are online