Database Management Summer 2003 The single entity, the single table, plus some basic SQL June 25

1. Database Management Summer 2003 The single entity, the single table, plus some basic SQL June 25

2. Data Modeling – Top-down approach Data Model Data Definition Database Table

3. The database development lifecycle (DDLC)… and the Term Project Assignments Assignment 2: Database Dictionary Assignment 1: Data Model Assignment 3: Database Prototype

4. Essential Terminology Entity A category representing a type of person, place, thing or event. In the OO world, this is called a “class.” Relation A two-dimensional table, with rows (or records) representing real-world instances of the entity in question, and columns (or fields) representing the attributes of interest. Identifier (primary key) A field (or combination of fields) that takes on a unique value for each record in the relation, and is used to distinguish that record from all others.

5. Primary Key • Unique • Not Null • Null • No Value • Not zero • Not empty string, “”

7. Data modeling – representing the single entity Watson’s looks like this: Asterix = primary key Ours will look like this: Underline = primary key

8. Database design for beginners: o An entity in the data model becomes a table (relation) in the database. o The attributes of the entity in the data model become the fields (columns) in the table in the database. o The entity’s unique identifier in the data model becomes the table’s primary key in the database. Plus: Instances are represented by records (rows) in the table.

9. Creating the actual table in the actual database … Your RDBMS will typically give you two options… o QBE (Query By Example), a GUI-based interface for creating, as well as modifying, populating, and querying database tables. o The SQL Create Statement. Example: CREATE TABLE TRACK (Trkid CHAR(4) NOT NULL, Trknum INT(4), Trktitle CHAR(30), Trklength Decimal(4,2), PRIMARY KEY(Trkid) )

10. SQL Server Data Types A subset of commonly used SQL Server datatypes

11. SQL (Structured Query Language) 4GL, Non-procedural language for working with relations • INSERT • UPDATE • DELETE • SELECT Create records Change records Remove records Retrieve records

12. INSERT INTOshrVALUES('FC','Freedonia Copper',27.5,10529,1.84,16) UPDATEshrSETshrprice = 31.50 WHEREshrcode = 'FC' SELECT*FROMshr DELETE FROMshr WHEREshrfirm = 'Burmese Elephant' SQL Syntax

13. Query Options • The SELECT statement: Retrieving records. • Retrieving selected fields, or “projection.” • Retrieving selected records, or “restriction” (WHERE clause, logical AND, logical OR, comparison operators, IN & NOT IN). • Ordering columns. • Ordering records (ORDER BY, DESC). • Derived data through SQL functions (COUNT, AVG, SUM, MIN, MAX). • Creating an alias for a results column (AS) • Pattern matching (LIKE, %, _ ) • Eliminating duplicate records (DISTINCT)

15. PROJECT

17. Query Functions and Operators • Arithmetic: + - * / • Aggregate: sum, avg, max, min • Comparison: =, <=, >=, >, <, <> • between • Logical: not, or, and • Set: count, distinct, in

18. Subquery • Select firm that has the maximum price SELECT shrfirm, shrprice FROM shr WHERE shrprice = (select max(price) from shr)

19. Report a firm’s name and price–earnings ratio. SELECT shrfirm, shrpe FROM shr Get all firms with a price-earnings ratio less than 12. SELECT * FROM shr WHERE shrpe < 12 Report firms whose code is AR. SELECT * FROM shr WHERE shrcode = 'AR' Report data on firms with codes of FC, AR, or SLG. SELECT * FROM shr WHERE shrcode IN ('FC','AR','SLG') List all firms where PE is at least 12, and order by descending PE. SELECT * FROM shr WHERE shrpe >= 12 ORDER BY shrpe DESC List all firms with a name starting with ‘F’. SELECT shrfirm FROM shr WHERE shrfirm LIKE 'F%' Find the number of different PE ratios. SELECT COUNT(DISTINCT shrpe) AS ‘Unique PE' FROM shr