Database Systems
CSCI 3343 -  Fall 2018 - Tentative Schedule - 2:10 TT

All Labs/Assignments Are Due The Next Class Period Unless Specified Otherwise!
   
Ben - Cloud-9 Caroline - Cloud-8 Reed- Cloud-4 Quentin - Cloud-3 Yusuf- Cloud-2 Dr. Hicks


# Topic(s) Reading Assignments
 & Handouts
Laboratory Assignment

#1

8/23

TH


Introduction To Class
Discuss Course Outline

Distribute Passwords
Configure Database Servers

 

 Course Outline

Windows-Database-Server-Installation-1

 

Questionnaire

Server-Configuration-1-HW


# 2

8/28

TU


 
DBMS
MySQL Workbench
Log Into MySQL

MySQL Review 1
SHOW DATABASES
CREATE DATABASE
DROP DATABASE
USE
CREATE TABLE
DROP TABLE
DESCRIBE
SHOW FIELDS IN
SELECT * vs SELECT x,y,z
COUNT(*) vs COUNT(First)
SELECT ... WHERE
LIKE "X%"
ORDER BY
LIMIT
SELECT 2 * 3 + 5
SELECT POW()2,8)
CONCATENATE ("ABC", "DEF")
LEFT ("Trinity", 2)
RIGHT
 ("Trinity", 2)
UPDATE
TINYINT, SMALLINT, MEDIUMINT,
BIGINT, CHAR, VARCHAR, BLOB,
MEDIUMBLOB, LONGBLOB,
DATE, TIME, TIMESTAMP, YEAR
SELECT CURDATE() --> MYSQL
SELECT CURTIME()
--> MYSQL
HOUR(X), MINUTE(x), SECOND(x)
DAY(x), MONTH(x), YEAR(x)
Relational Queries
Primary Keys
Foreign Keys
 

Windows-Database-Server-Installation-2

MySQL Tutorial

Library1.zip

V_MySQL Tutorial.mp4

 

Server-Configuration-2-MySQL-1-HW

Server-Configuration-2-MySQL-1-HW.docx

# 3

8/30

TH


MySQL
ASCII(('A'), CHAR(65)
FLOOR(12.3), CEILING(12.49)
RAND(), Rand No 5-10
MID("Thomas",2,2)
Substring("Thomas",2,2),
Delete
Sum(Price)
Min(Price)
Max(Price)
Max(Price) AS MaxPri e
Natural Join Tables
Navicat
Update
Queries With Natural Joins

Satabase Design
 Users & Courses
Database Management System (DMS)
Massive Scale
Persistent
Multiple Users
Algorithm Independence}
High Level Query Language}
Database Performance
UpTime - Reliability
Data Definition Language
Data Manipulation Language
Database Design
Relational Queries
  

Library2.zip   

V_Introduction-To-Databases.mp4

V_Querying-Relational-Databases.mp4

V_Querying-Relational-Databases.mp4

V_Relational-Algebra-1.mp4

V_Relational-Algebra-2.mp4

MySQL-2-Intro-To-Databases-HW

MySQL-2-Intro-To-Databases-HW.docx

# 4

9/4

TU

 

Internet Information Services (IIS)
Install & Configure IIS
Install & Configure ASP & PHP
Internet Information Services Manager
Navicat

Adv Relational Queries

 

IIS-Web-PHP-Configuration

V_01-SQL-Introduction.mp4


IIS-Web-PHP-Configuration-HW


# 5

9/6

TH


 HTML, WWW, URL, WYSIWYG
Hyper Text Markup Language
Tim Berners Lee
WYSIWYG Editors
<HTML> </HTML>
<BODY> </BODY>
<HEAD> </HEAD.
<TITILE> </TITLE>
<BR> <P> <//P>
<H1> <H2> <H3> <H4> <H5> <H6>
<HR>
<CENTER> </CENTER>
<B></B>
<U> </U>
<I> </I>
<IMG SRC = "X.jpg">
<A HREF = "HTTP://GOOGLE.COM">
Relative vs. Absolute Addressing
<TABLE> </TABLE>
<TR> </TR>
<TD> </TD>

Data Import/Export

Adv Relational Queries

   

Intro-HTML-Web  Slides  PDF

V-02-SQL-Basic-Select.mp4

V-03-SQL-Table-Variables-Set-Operators.mp4

V-04-SQL-Subqueries-In-Where.mp4

Intro-HTML-Web-HW

Intro-HTML-Web-HW.doc

# 6

9/11

TU

 

Quiz 1 - 20 Minutes
Lectures 1-4

Using GUI's For Query Creation
Using GUI's For Web Design
Install Flyspeed

Adv Relational Queries

 

Flyspeed-Expression-In-Class-Lab-Directions

V-05-SQL-Subqueries-From-Select.mp4

 

FlySpeed-Expression-In-Class-Lab-HW.doc

FlySpeed-Expression-In-Class-Lab-HW.zip

# 7

9/13

TH


Intro To PHP
 

PHP Hypertext Processor
Rasmus Lerdorf in 1995
MiddleWare Language
Open Source
<?PHP ... ?>
print "<H1> Hello</H1>";
echo "<H1> Hello</H1>";
Dynamic Variablez $Name
Integer, Double, String, Boolean,
Object, & Array
Comments // # /* .. */
$Name = "Tom";
$Stooges = array("Larry", "Curley", "Moe“);
print "x = " . $x . "<BR>";
gettype("Tom")
settype ($Quantity, "integer");
+ - * / % += *= -= /= %=
== != > < >= <= || or && and !
constant ==> define ("PI", 3.14);
if then elseif else
switch ($X) { case 5: break }
while ($pos <= 50) {...};
do {...} while ($pos <= 50);
for ($pos = 1; $pos <= 80; $pos ++){...}
Functions With No Returns
Functions With Explicit Returns
Function Arguments
Global Variables
Array Processing
ASort(Stooges)
Array_Push
Array_Merge
Array_Shift
 
 

 

Intro-PHP  Slides  PDF

University1


Intro-PHP-HW

Intro-PHP-HW.doc

# 8

9/18

TU

 
PHP - MySQL

mysqldump -ptrinity -uroot stanford >
c:\temp\stanford2.sql
mysql -ptrinity -uroot stanford2 < c:\temp\stanford2.sql
Creating Users
Setting User Permissions
PHP Connection To MySQ
$server ="localhost";
$username="root";
$password="trinity";
$database="libraryth1";
$con=mysqli_connect($server, $username, $password, $database);
$query = "SELECT * FROM Users";
$recSet = $conn->query($query);
while(list($sID, $sName, $GPA) = $recSet->fetch_row())
printf("<strong>%3d %-15s %4.2f </strong> <BR>", $sID, $sName, $GPA);

while($Rec = $recSet->fetch_row())
printf("<B>%3d %-15s %4.2f </B> <BR>", $Rec[0], $Rec[1], $Rec[2]);
API
PHP Extensions - Configure php.ini
mysql vs. mysqli
mysqli commands
multiple queries - one connection
include_once "../../Secure-Connect/Connection.php";
$query1 = "SELECT COUNT(*) AS NoStudents FROM Student";
-- One Item In Query Results --
$recSet1 = $con->query($query1);
$Rec = $recSet1->fetch_row();
print "No Students = " . $Rec[0] . "<p><hr>";
Stanford-Student-Apply
Expression Web - Configure Page
Expression Web - Nicely Formatted Output
Web Page Output From Multiple Table Queries
 

PHP-MySQL  Slides  PDF

Library1.zip


PHP-MySQL-HW

Due 9/25



# 9

9/20

TH


PHP - MySQL

mysqldump -ptrinity -uroot stanford >
c:\temp\stanford2.sql
mysql -ptrinity -uroot stanford2 < c:\temp\stanford2.sql
Creating Users
Setting User Permissions
PHP Connection To MySQ
$server ="localhost";
$username="root";
$password="trinity";
$database="libraryth1";
$con=mysqli_connect($server, $username, $password, $database);
$query = "SELECT * FROM Users";
$recSet = $conn->query($query);
while(list($sID, $sName, $GPA) = $recSet->fetch_row())
printf("<strong>%3d %-15s %4.2f </strong> <BR>", $sID, $sName, $GPA);

while($Rec = $recSet->fetch_row())
printf("<B>%3d %-15s %4.2f </B> <BR>", $Rec[0], $Rec[1], $Rec[2]);
API
PHP Extensions - Configure php.ini
mysql vs. mysqli
mysqli commands
multiple queries - one connection
include_once "../../Secure-Connect/Connection.php";
$query1 = "SELECT COUNT(*) AS NoStudents FROM Student";
-- One Item In Query Results --
$recSet1 = $con->query($query1);
$Rec = $recSet1->fetch_row();
print "No Students = " . $Rec[0] . "<p><hr>";
Stanford-Student-Apply
Expression Web - Configure Page
Expression Web - Nicely Formatted Output
Web Page Output From Multiple Table Queries

 

PHP-MySQL  Slides  PDF

Library1.zip

PHP-MySQL-HW
Due 9/25

# 10

9/25

TU

 

HTML Forms & Database

Faculty-Add.php
<FORM></FORM>
Actions : Get vs. Post
<FORM METHOD = "POST" ACTION = "xx.php" ID = "f1" NAME = "f1">
<INPUT TYPE ="submit" VALUE ="Button MSG!">
First <INPUT NAME = "First" TYPE = "text" SIZE = "15">
$First = $_POST['First'];
print "First = " . $First . "<BR>";
$server ="localhost";
$username="User";
$password="tri";
$database="univ";
$con=mysqli_connect($server, $username, $password, $database);
$Query = "INSERT INTO Faculty (First, Last) " .
"VALUES (" . $First . ", " . $Last . "); " ;
if ($testing)
Manually Testing Query
Deleted <INPUT NAME ="Deleted" TYPE ="checkbox">
if(empty($_POST['Deleted']))
$Deleted = 'F';
else
$Deleted = 'T';
Department <INPUT TYPE = "checkbox"
NAME = "Department"
VALUE = "Computer Science"> Computer Science
<INPUT TYPE = "checkbox"
NAME = "Department"
VALUE = "Mathematics" > Mathematics
Gender <INPUT TYPE = "radio"
NAME = "Gender"
VALUE = "1"> Male
<INPUT TYPE = "radio"
NAME = "Gender"
VALUE = "2"> Female
DeptID <select name="DeptID">
<option value="1">Computer Science</option>
<option value="2">Biology</option>
<option value="3">Chemistry</option>
<option value="4">Engineering Science</option>
</select>
<INPUT NAME = "Secret"  TYPE = "hidden"
VALUE="Computer Science Rocks">
Notes <TEXTAREA NAME = "Notes"
COLS = "70" ROWS = "7">
Enter your notes here... </TEXTAREA>
Building & Testing Queries
PHP Redirection - Function
header("Location: http://google.com");
Nicely Formatted Forms

Advanced Queries With Aggregation
Queries With AS
 

Forms-PHP-MySQL  Slides  PDF

University1.zip

University2.zip


Faculty-Display.zip

V-06-SQL-Aggregation.mp4
Forms-PHP-MySQL-HW.doc

Forms-PHP-MySQL-HW.pdf

# 11

9/27

TH

  
B Trees & B+ Trees
Intro To
Bayer & McCreight
Relational Databases
M-Way Trees
B Tree Node
B+ Tree Node
Adding Data - Rotations
Rebalancing Trees
Tracks - Sectors - Blocks
Buffers
  Calculate Optimal M for B Tree
  Calculate Optimal M for B+ Tree

Data Modification

Create Index Title On Books(Title)
What To Index
Things You Search
Order By
Primary Key
Foreign Keys In Queries (Joins)
Foreign Keys In Views (Joins)
You Can Index Too Much
Indexing Slows Down
Insert, Delete, Update(sometimes)
Indexing Improves Performance
Hash Tables  vs. B+Trees

Cross Join
Natural Join
Inner Join
Outer Join
Full Join
Database Queries Utilizing Joins


B+Trees  Slides  PDF


V-09-SQL-Data-Modification.mp4

LibraryTH.zip
B+Tree-HW

B+Tree-HW.doc

# 12

10/2

TU

 Quiz 2 - 20 Minutes
Lectures 5-9


 Lectures/Indexing  Slides  PDF

LibraryIndex.zip


V-11-SQL-Indexing-1.mp4

V-10-SQL-Join-Operators.mp4



BigData1.student.php.zip

EmptytableImport.zip

BigDataKey.zip

 

Indexing-HW

Indexing-HW.doc



TEAM -->  PHP-Research-1-HW


# 13

10/4

TH

  
Microsoft SQL Server
Installation
Configuration


Microsoft SQL Management Studio
Installation

Transaction Processing



 Windows-Database-Server-Installation-3
 
LibraryTH-13.zip

Stanford-13.zip


V-12-SQL-Intro-To-Transactions.mp4

V-13-SQL-Transaction-Properties.mp4

V-14-SQL-Isolation-Levels.mp4

   

Server-Configuration-3-MSSQL-1-HW
# 14

10/9

TU

 

Finish PHP Team Research #2

 

University1.zip

University2.zip

TEAM -->  PHP-Research-2-HW
Due 10/15

# 15

10/11

TH

Constraints & Triggers
Entity Framework

 

Transaction Processing
    Handle Database Concurrency
System Failures
Attribute Level Inconsistency
Tuple Level Inconsistency
Table Level Inconsistency
Multi-statement Inconsistency
Query To Fill One Table From A SELECT
Concurrency Goal - Isolation
All Or Nothing Guarantee
Commit
AutoCommit
ACID
Automacity Property
Consistency Property
Does Not Guarantee Correctness
Isolation Property
Durability
Serializability
Dirty Database

   


Entity Framework Tutorial

Transaction-Management-Slides.pdf


V-16-SQL-Constraints-Several-Types.mp4


V-17-Transactions-intro.mp4

V-18-Transactions-Properties-Part-1.mp4

V-19-Transactions properties part2 .mp4

 

Entity-Framework-Web-Site-HW
Due 10/18

Transaction-Management-HW.pdf
Due 10/30

# 16

10/16

TU

 Quiz 3 - 20 Minutes
Lectures 10-14

Entity Framework

   
Entity Framework.pdf


Entity-Framework-Web-Site-HW
Due 10/30

# 17

10/18

TH

  
Normalization & Database Design

Database For Your Resume
Spreadsheets - Poor Excuse For Database
Database Interfaces
Server-Side Interfaces
Client-Side Interfaces
Server Architecture Model
Client Architecture Model
Internet Architecture Model
Middleware
Web Servers IIS/Apache
Edgar Codd
Larry Ellison - Oracle
Codd's Anomalies
Update/Delete/Insert
Normalization
Why Normalize
1st, 2nd, 3rd Normal Forms
Jeff Putman Warning
Database Design
 


Normalization-Access-DB.pdf

 


# 18

10/23

TU


Access Database
Create Database
Create/Edit Tables
Create Run-Time Form
Create Custom Run Time Form
Form Navigation Buttons:
Next,Previous, First, Last
Add & Delete Records
Edit Tables
Work On Access Lab

Full Convert To Convert Database

Entity Relationship
Entity - Rectangle
Weak Entity - Double Recctangle
Attributes/Fields - Ellipse
Relationship/Action - Diamond
  


Entity Relationship

 
V-15-SQL-Constraints-Triggers-Overview.mp4

LibraryTH.zip

   
Access DB-HW.pdf

# 19

10/25

TH

 
Normalization & Database Design

Codd's Anomalies
Update/Delete/Insert
Normalization
Why Normalize
1st, 2nd, 3rd Normal Forms
Jeff Putman Warning
Database Design
Elementary School Library Database Design
 


Normalization-Access-DB.pdf


1 HR Working On Design 1


# 20

10/30

TU

 Quiz 4 - 20 Minutes
DB Queries On All Stanford Videos V1-V15


Practical Transactions
Using MySQL


Team-1-Suppliers-Student.zip

Normalization-Access-DB-HW.pdf
Due 11/6

# 21

11/1

TH

Visual Studio C# Form Design I

VS-1-Basic-Form-Tutorial


TrinityLibrary1.zip

   
Windows Forms Application 1-HW
Due 11/6


# 22

11/6

TU


Visual Studio C# Form Design II

VS-2-Buttons-TabFrame-Form-Tutorial
   
TrinityLibrary2.zip
Windows Forms Application 2-HW
Due 11/8

# 23

11/8

TH

  Dpt Net Database Connections 
No SQL Databases

VS-3-MySQL-Connection-Tutorial

Diagnostic_Testing.txt

Diagnostic_Testing.zip



Videos/V-27-JSON-Introduction.mp4

MongoDB Tutorial 1 What is MongoDB.mp4

MongoDB vs MySQL.mp4

NoSQL-MongoDB-1-HW
Due 11/13


# 24

11/13

TU

  Dpt Net Database Connections 
No SQL Databases


Server-Configuration-4-MongoDB-HW

 

 

# 25

11/15

TH


Visual Studio C# Form Design IV


VS-4-MySQL-ComboBox-CheckBox-Tutorial

Implement Tutorial 4
Place the Project In C:\LibraryApps


# 26

11/20

TU


Work On Project
Independently Outside Of Class




   
Implement Tutorial 4
Place the Project In C:\LibraryApps

# 27

11/22

TH


THANKSGIVING
 
FinalProject-Windows-Forms-3-6-HW
Not Accepted After 5:00 on 12/7/18

# 28

11/27

TU

Visual Studio C# Form Design IV


VS-5-MySQL-Navigation-Record-Processing-Tutorial

Implement Tutorial 5
Place the Projects In C:\LibraryApps

FinalProject-Windows-Forms-3-6-HW
Not Accepted After 5:00 on 12/7/18



# 29

11/29

TH

MongoDB Queries

MongoDB-2-Slides


NewTrinityLibrary2.zip

MongoDB-2-HW
Due By Noon On 12/5

FinalProject-Windows-Forms-3-6-HW
Not Accepted After 5:00 on 12/7/18

# 30

12/4

TU

      

Quiz 5 ==> 20 - 30 Minutes
Outside Of Class
Tues Morning : 9-12 AM
Wed Morning : 9:00-12:00

Optional Retake Quiz 4
Quiz 20 - 30 Minutes

Work On Add/Edit Mode
Normal/View Mode

 


VS-6-MySQL-View-Management-Tutorial


 

FinalProject-Windows-Forms-3-6-HW
Not Accepted After 5:00 on 12/7/18

R

12/5

W





    READING DAY
Available 9:00-12:00






    READING DAY






    READING DAY



R

12/6

TH





    READING DAY
Grading At Home






    READING DAY






    READING DAY


+

No Assignments Will Be Accepted After 5:00 PM On 12/7