BA371 Business Systems Design - Relational Model & SQL

 

• Developed by E.F. Codd in 1970.
• Founded on relational algebra. 
• Prevalent DBMS model.
• Relation:
• Rows or records or tuples represent things or entities.
• Columns or fields or attributes,  represent characteristics of the entity.

• Columns represent but a single attribute.

• Not allowed: colored cells in a spreadsheet.
  
• Cells hold single values.
• All rows describe the same type of entity.
• Column names are unique.
• Row and column order are irrelevant.
• Rows are unique.

• What can we store in a cell (column):
• Characters: ASCII (American Standard Code for Information Interchange) and Unicode characters.
• String (n bytes).
• Number (integer, float, size).
• Date or date/time. 
• Bytes (BLOBs: Binary Large OBjects; e.g., image, binary file, text file, etc.).
• A word on dates:
• Manufacturer-provided date types:
• Oracle: 7 bytes: century, year, month, day, hour, minute, second.

• You understand how this works?
  
• Microsoft SQL Server:
• Datetime: 8 bytes (accuracy: 3/100 second).
• Smalldatetime: 4 bytes (accuracy: 1 minute).

• Microsoft Access: 8 bytes (accuracy: 1 second).

• However, assume the following (near) real time system:
• 500 data sources with 1-second accuracy.
• 1 data point per source per minute.
• = 500 x 60 = 30,000 data points per hour.
• = 30,000 x 24 x 365 = 262,800,000 data points per year.
• = 262,800,000 * 7 bytes = 1,839,600,000 ≈ 1.8 GB of date/times only!!!
  

• Does not include the observed values).

• Size doubles when a DB index is added.
  
• Takes RDBMS time to find data.

• Problem: Can we do better? i.e., can you come up with a different date/time model that still is accurate to the second yet saves lots of space? At what price?

• The contents of a row is functionally dependent on its primary key:
• Primary key uniquely indentifies a row.
• Specification of a primary key is known as a primary key constraint.
  
• Relationships may require the existence of the key in the associated relation ==> foreign or referential key.
• Specification of a foreign key is known as a foreign key or referential integrity constraint.
• Surrogate keys save space.
  

• Normal form (p. 202-207):
• Normalization: the process of eliminating relational redundancy (≠ semantic or storage redundancy (see below)) and implicit relationships.
• Nonnormalized tables can spell trouble:
  
• Storage anomaly: if the same relation is stored more than once you're using more storage than necessary.
• Update anomaly: if the same relation is stored more than once, changes to that information require it to be changed at multiple locations:
• Time and effort.
• Opportunity for error.

• Automating multiple updates introduces needless complexity.
  

• Deletion anomaly: embedding a relation in another relation introduces the loss of both upon removal of the outer one (e.g., Fig. 11.6).
  
• Six orders of normal form;  (Codd defined first, second and third normal form):

• First Normal Form (1NF): Table is a relation.
  
• Second Normal Form (2NF): 1NF and every nonprimary key attribute is functionally dependent on the entire(!!!) primary key:

Order Number*	Part Number*	Part Desc	Number Ordered	Quoted Price
12487	AX12	Iron	2	$21.95
12491	BT04	Grill	1	$149.99
12491	BZ66	Washer	1	$239.99
12494	CB03	Stove	1	$499.99
12495	CX11	Blender	5	$49.99
12498	AZ52	Vacuum	2	$199.99
12498	BA74	Furnace	1	$1999.99
12500	BT04	Grill	2	$169.99
12504	CZ81	AC	1	$4999.99




• Part Desc is functionally dependent on Part Number but not on Order Number.
  
• Notice how the table has a composite primary key.
• Solution: split into two tables.
• Important: whether or not a table is in 2NF cannot be established by inspecting any or all of the records stored in the table.
  
• Problem: Can you force a table into 2NF without deleting information or the creation of any new tables?
  

• Third normal form (3NF): in 2NF form and no nonprimary key attribute is transitively dependent on the primary key.
  • Transitive dependency of C on A: A, B and C are attributes such that B functionally depends on A, and C functionally depends on B (A --> B--> C).

    Cust Number*	Cust Last	Cust First	Balance	Sales Rep Number	Sales Rep Last Name	Sales Rep First Name
    1	Jones	John	$1000	112	James	Mary
    2	Smith	John	$1001	113	David	Mary
    3	King	John	$1002	112	James	Mary
    4	Sullivan	John	$1000	112	James	Mary
    5	Robbins	John	$1003	103	Scott	Mary

  • Primary Key: Customer Number
  • Sales Rep First Name and Sales Rep Last Name depend on Sales Rep Number; SalesRep Number depends on Customer Number
  • Solution: split into two tables.
  • Again!! functional and transitive dependencies are inherent; a sample of data will not provide the relationship!

• Relational redundancy vs. semantic redundancy or storage redundancy: 
• Surrogate Keys: e.g., Customer Name vs. Customer Id or Date_int vs. Datetime.
  
• Total Price stored vs. Total Price computed. (storage redundancy) --> data warehouse to the rescue!
  

• Sometimes it is necessary or desirable to purposefully violate normal form:
  • Speed: Normality violation implies fewer tables ==> (often) faster queries.
  • App. code complexity.

• Null values: 
• Two interpretations:

• NA: structural null.
• Unknown (missing).
  

• DBMSs require a decision on nullability of column values.
• Primary keys cannot be null.
  

• Notice that a database is essentially a self-describing repository of relationally organized data:

• Database's metadata are kept in a relational model known as the data dictionary.

• Because of the data dictionary, one relational database management system (RDBMS) can hold and manage multiple databases.
  

• Kroenke  (2006) on Structured Query Language (SQL): "consists only of constructs for defining and processing a database."

• Many RDBMSs provide interfaces for graphically defining and processing a database; Query By Example (QBE).
• Direct manipulation (GUI) vs. language-based interaction:
• Direct manipulation interfaces or GUIs (typically, but not necessarily) 'ride' on top of SQL.
• Advantages: 
• Don't need knowledge of SQL to define or process a database.
• GUIs can be more intuitive than language constructs; hence .Net visual programming.
  
• GUIs abstract from many details ==> user leaves control to the system.

• Disadvantages:

• Languages are almost always richer (more expressive) than GUIs => with languages you can do more.
• Since the GUI (typically) communicates to the RDBMS through the language, proper understanding of database processing requires knowledge of the language.
• Language (at least the standard) is portable; with GUIs it depends.
• !!! Programs are not good at pushing buttons !!! SQL provides a priceless API.

• SQL history:
• Developed at IBM in 1970s.
• Standardized by ANSI/ISO 1987 and 1992 (SQL2)

• e.g., data types: "SQL defines distinct data types named by the following <key word>s:
  CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC, DECIMAL,
  INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME,
  TIMESTAMP, and INTERVAL."
• RDBMS manufacturers typically add SQL extensions; e.g., Oracle's BLOB.
• The combined result of standard and extensions is an SQL dialect or native SQL.
• Using extensions improves convenience and performance but reduces portability.

• Why do we use Access before we use industrial strenth systems such as SQL Server, Oracle, MySQL, etc.?
• Access provides all functionality to become familar with the relational world.
• Databases are single files that can be carried around.
• No server administration.

• Running SQL commands in Access:
• Note that since in Access much graphical data manipulation gets translated into SQL before being evaluated by the database engine, you can submit 'raw' SQL to Access instead of going through the QBE interface.
• To enter and run SQL commands in Access 2003:
• Open an Access 2003 database.
• Select Queries.
• Double click Create Query in Design View.
• Close the Show Table dialog. Note how the 'new' icon changes into an SQL icon (left side of toolbar).
• Select SQL View from the SQL icon menu.
• You now have a text window into which you can enter the text of an SQL query.
• To run the query, select the ! icon from the toolbar.

• To enter and run SQL commands in Access 2007:
• Open an Access 2007 database.
• Create tab --> Query Design.
• Close the Show Table dialog. Note how the 'Table' icon changes into an SQL icon (left side of toolbar).
• Select SQL View from the SQL icon menu.
• You now have a text window into which you can enter the text of an SQL query.
• To run the query, select the ! icon from the toolbar.

• SQL supports all aspects of relational database definition and processing:
• DB or schema definition:
• Table creation:
• Microsoft Access dialect:
• create table project
  (project_id integer not null,
   project_name text not null,
   dept_id text,
   maxhours integer);
• Notice that SQL commands are case-insensitive and are terminated with a semicolon.
  

• Oracle dialect:
• create table project
  (project_id integer not null,
    project_name varchar(64) not null,
    dept_id varchar(64),
   maxhours integer)
    tablespace admin_data
    storage (initial 120k next 40k pctincrease 25);
• Notice the 'extra' (optional) specifications on where the table will get stored an on how to allocate fresh storage when space fills up; i.e., physical design.

• Table deletion:
• drop table project;

• Adding constraints:
• Primary key constraints:
• alter table project add constraint pk1 primary key (project_id);
• Note: it is possible to embed this and other constraints in the create statement. However, it is good habit to keep these definitions separate.
  

• Foreign keys:
• alter table project add constraint fk1 foreign key (dept_id) references department(dept_id);
• Notice that when only the reference table is mentioned, its primary key is implied:
• alter table project add constraint fk1 foreign key (dept_id) references department;

• Careful!! A table won't drop if a foreign key constraint on another table refers to one or more of its fields!
• Therefore, apply Last In First Out (LIFO) logic; i.e., 
• Creation:
1. Create the tables.
2. Add the constraints.
• Deletion:
1. Delete the constraints.
2. Drop the tables.

• alter table project drop constraint fk1;
• drop table department;

• Database creation and drop scripts:
• The process of database design and creation is often iterative; designers go through several versions of the schema and test their schema designs before settling on a final design.
• Sometimes a copy of the database schema (database = schema + contents) must be implemented elsewhere.
• It is good practice to code both your database creation and drop sequences in different SQL scripts:
• create_tables.sql
• add_primary_key_constraints.sql
• add_foreign_key_constraints.sql
• drop_foreign_key_constraints.sql
• drop_tables.sql
  

• DB editing (insert, delete and update):
• Inserting records:
• insert into project values (1600, 'Q4 Tax Prep', 1, 100);
• Notice that SQL strings are delimited by single quotes. Strings within these quotes are case-sensitive.
  
• Column order can be specified as follows:
• insert into project (project_name, project_id) values ('Q4 Tax Prep', 1500);

• Some entries require special syntax: e.g.,
• create table foo (my_date date not null);
• Microsoft Access: insert into foo values (format('01/01/1997','mm/dd/yyyy'));
• Oracle: insert into foo values (to_date('01/01/1997','mm/dd/yyyy'));

• Deleting records:
• delete from project;
• delete from project where project_id > 2000;
• delete from project where project_id in (1010, 1020, 1030, 2000);
  

• Notice that the where clause qualifies (puts constraints on) the delete.

• Oracle dialect: truncate vs. delete: truncate deletes and reclaims storage space.

• Updating records:
• update project set project_name = 'Q2 Tax Preparation' where project_id = 1600;
• Updates can be replaced by a delete followed by an insert.