Database Management Essentials: Modeling, Design, and SQL Syntax

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