CPS 353: Internet Programming

Database Schema (and maybe some other sql)

Marcos Elugardo

Gordon College

Last Modified: 10/03/2015

Selected content adapted from material by Kirk McClure.

Agenda

• Check-in
• Syllabus change
• Database schema
• Stored procs and sql (if time)
• Class Research topics
• Homework 4
• Milestone 4

Mission statement

• What is the purpose of this database?

Mission statement

• What is the purpose of this database?
• The purpose of the Fantasy Football database is to maintain the information for managing a fantasy football team and enable knowledge-based performance enhancements.

Define Mission Objectives

• Mission objectives describe general tasks to be supported by the database. They are generally defined in cooperation with end users

Mission Objectives

• Track historical player performance data.
• Track current player performance.
• Maintain current fantasy league information.

Identify the nouns

• What are the things we want to model in the database?
• This again may come from talking to end users or from examing any existing systems (our legacy ones and those of our competators)

Identifying Characteristics

• What are the characteristics of the nouns that we want to track?

Review and Refine the List of Characteristics

• One suggestion is to expand the list by prefacing generic terms like "Name" with the associated subject. (OwnerName, TeamName)

Identify calculated characteristics

• Make a note of and remove them from our list since this is what we will derive our tables from. Calculated fields do not need to be stored
• The general rule is: store the basis for the calculation rather than the result.

Defining the Final Table List

• Names are refined for clarity, brevity, generality, and plural form.
• The description addresses
• What - object description and contents
• Why - statement of importance

Some table examples

Associate fields with tables

• This activity starts with assigning fields from the preliminary field list to each table.
• Refine the field names. Create a unique, descriptive name that accurately, clearly, and unambiguously identifies the characteristic
• Resolve multivalue fields. Name = First_Name + Last_Name

Ideal fields

• Represent a distinct characteristic of the subject.
• Contain a single value.
• Are not calculated or concatenated.
• Are unique within the database.

Ideal tables

• Represent a single subject (either object or event).
• Has a primary key.
• Does not contain multipart or multivalued fields.
• Does not contain calculated fields.
• Does not contain unnecessary duplicate fields.
• Contains the minimum required redundant data.

Primary key

• Table-level integrity is established by the use of primary keys
• A primary key is selected from the list of candidate keys for each table
• If there is not one, then one needs to be created.

Elements of a primary key

• Cannot be a multipart field.
• Must contain unique values.
• Cannot contain null values.
• Cannot compromise security or privacy. (Never use Social Security Number.)
• Value cannot be optional in whole or in part.
• Uses minimum number of fields necessary for uniqueness.
• Uniquely and exclusively identifies each record.
• Exclusively identifies each field within a record.
• Value is rarely modified.

Define data type for fields

• Specify int, varchar, Datetime data types

Table relationships

• Identify how the tables relate to each other.
• If I have one owner record, how many team records can I have?
• If I have one team record, how many owner records can I have?
• If I have one team record, how many matchup records can I have?
• If I have one matchup record, how many team records can I have?
• A matrix might be helpful with each axis being the tables and the intersections identifying the type of relationship

Foreign Keys

• A foreign key is a field in a table that matches the primary key column of another table.
• Guidelines:
• It has the same name as the primary key in the referenced table.
• It uses a replica of the field specifications for the primary key in the referenced table. (data type)
• It draws its values from the primary key to which it refers.
• There are times where the foreign key name is different than the primary key (like in the match ups table)

Type of participation

• Mandatory: There must be at least one record in this table before entering any records into the other.
• There must be a record in league before there are records in team
• Optional: There is no requirement for any records to exist in this table before entering records into the other table.
• If there are no records in the defense performance before there are records in the roster, that is ok.
• This knowledge helps when creating queries

Creating a table

CREATE TABLE [dbo].[Course]
(
	[CourseId] INT NOT NULL PRIMARY KEY IDENTITY, 
	[Title] VARCHAR(50) NOT NULL,
	[Description] VARCHAR(MAX),
	[StartDate] DATETIME2 NULL
)

• A table that has a primary key that is autoincremented
• Course id in an integer, Title is a string that cannot be longer than 50 characters
• Description has no limit
• StartDate contains the date the course starts in a datetime format

Creating a table - 2 foreign keys

CREATE TABLE [dbo].[Enrollment]
(
	[EnrollmentId] INT NOT NULL PRIMARY KEY IDENTITY, 
    [StudentId] INT NOT NULL, 
    [CourseId] INT NOT NULL, 
    [Grade] INT NOT NULL DEFAULT 100,
	CONSTRAINT [FK_Enrollment_Student] 
		FOREIGN KEY ([StudentId]) REFERENCES [Student]([StudentId]),
	CONSTRAINT [FK_Enrollment_Course] 
		FOREIGN KEY ([CourseId]) REFERENCES [Course]([CourseId])
)

• Start with FK_
• Name the table the key is in followed by an underscore
• End the name of the key with the table the key is refering to

SQL for fantasy football tables

This is part of your homework. The sql in sql server is a little differnt than in mysql, so do not take examples from that database.

SQL

• Structured Query Language
• Create tables
• Select data
• Update data
• Delete Data
• Calculations

Tables

Join

SELECT gid, first_name, last_name, pid, gardener_id, plant_name 
FROM Gardners
 JOIN Plantings
ON gid = gardener_id

• An inner join, or just join, produces a result set that is limited to the rows where there is a match in both tables for what we're looking for. If you don't know which kind of join you need, this will usually be your best bet.

Left Join

SELECT gid, first_name, last_name, pid, gardener_id, plant_name 
FROM Gardners
LEFT JOIN Plantings
ON gid = gardener_id

• A left join, or left outer join, results in a set where all of the rows from the first, or left hand side, table are preserved. The rows from the second, or right hand side table only show up if they have a match with the rows from the first table. Where there are values from the left table but not from the right, the table will read null, which means that the value has not been set.

Right Join

SELECT gid, first_name, last_name, pid, gardener_id, plant_name 
FROM Gardners
RIGHT  JOIN Plantings
ON gid = gardener_id

• A right join, or right outer join, is the same as a left join, except the roles are reversed. All of the rows from the right hand side table show up in the result, but the rows from the table on the left are only there if they match the table on the right. Empty spaces are null, just like with the the left join.

Outer Join

SELECT gid, first_name, last_name, pid, gardener_id, plant_name 
FROM Gardners
 OUTER JOIN Plantings
ON gid = gardener_id

• A outer join, or full outer join, produces a result set with all of the rows of both tables, regardless of whether there are any matches. Similarly to the left and right joins, we call the empty spaces null.

Cross Join

SELECT gid, first_name, last_name, pid, gardener_id, plant_name 
FROM Gardners
CROSS JOIN Plantings

• The cross join returns a table with a potentially very large number of rows. The row count of the result is equal to the number of rows in the first table times the number of rows in the second table. Each row is a combination of the rows of the first and second table.

Self Join

SELECT G1.gid, G1.first_name, G1.last_name, G2.gid, G2.first_name, G2.last_name
FROM Gardners G1
 JOIN Gardners G2 
ON G1.first_name = G2.first_name

• You can join a single table to itself. In this case, you are using the same table twice.
• Notice the table aliase

Combined joins

SELECT G1.gid, G1.first_name, G1.last_name, G2.gid, 
		G2.first_name, G2.last_name, p.plant_name
FROM Gardners G1
JOIN Gardners G2 ON G1.first_name = G2.first_name
JOIN Plantings p
ON G1.gid = p.gardener_id

Real world examples

• These are queries taken from some of the sql I was working on this month

Class Research topics on databases

• NoSQL databases
• SSIS
• Replication
• Different tenant models
• Performance
• there are several tons of stuff I did not cover that you could research