COP 2700

1
COP 2700 Data Structures (SQL)

• Lecture 1 May 18, 2015

2
Introductions

• Bradleys Contact Info
• bradleyl_at_palmbeachstate.edu
• 407-925-8751
• Bradleys Background

3
Syllabus

• Define Structured Query Language (SQL) and
  discuss its general applications.
• Explain various database architectures.
• Explain the relation between SQL and database
  operations.
• Outline the various SQL commands.
• Create database objects including tables, views,
  and indexes.
• Use SQL statements to populate tables.
• Use various forms of the SELECT statement to
  query the database including FROM, WHERE, GROUP
  BY, and ORDER BY.
• Use various forms of the INSERT, UPDATE and
  DELETE statements to update the database
• Use SQL statements to create and manage views.
• Use SQL statements to manage users and set data
  security.
• Develop simple SQL stored procedures and
  functions.
• Access databases through API calls using
  connection strings and SQL commands

4
Required TextBook

• A Guide to SQL
• 8th Edition
• Authors Pratt Last
• Published By Cengage
• ISBN-10 0-324-59768-1

5
Other Required Stuff

• You will need a thumb drive or your own laptop to
  work on in-class assignments and follow-alongs.
• You will need access to a PC with SQLServer
  installed to do the take home assignments. (The
  computer lab has been set-up with SQLServer
  installed.)

6
Course Outline

• Week1 Introduction Single Table Selects
• Week2 Memorial Day More Single Table Selects
• Week3 Database Design Multi Table Selects
• Week4 More Multi Table Selects MidTerm
  Database Updates
• Week5 Procedures, Functions and Triggers
• Week6 Accessing Databases through Web Pages
  Final Exam
• Week7 June29 Not Sure Yet

7
Grading

• 5 Homework Assignments 10 Points Each
• Single Table Select Statements
• Database Design and Normalization
• Multiple Table Select Statements
• Database Update Statements
• Procedures and Functions
• Midterm Exam June 8, 2015 15 Points
• Final Exam June 24, 2015 15 Points
• Attendance and Class Participation 20 Points
• 1 point for every class attended
• 1 point for completing class work and turning it
  in.
• There may be bonus points in some assignments
• Add up your points and apply the standard grading
  scale
• 90-100 A, 80-89 B, 70-79 C, 60-69 D,
  Below 60 F
• Im not sure what happens on June 29?? Will let
  you know.
• Six Weeks goes by very quickly. Take advantage
  of in-class lab time. Do your work early. Dont
  wait until the last minute to turn in assignments.

8
Grading

• Makeup exams are given only if there is solid
  evidence of a medical or otherwise serious
  emergency that prevented the student from
  participating in the exam.
• Assignments are to be submitted on time, with 2
  point per day late penalties. Appropriate
  accommodations will be made for students having a
  valid medical excuse for being unable to work on
  an assignment.
• Unless there is solid evidence of medical or
  otherwise serious emergency situation, incomplete
  grades will not be given.

9
Withdrawal Policy

• Students who miss more than three days of class
  without notifying the professor of why they have
  not attended class before June 15, 2015, will be
  withdrawn from the class and receive a WX.
• Regardless of reason, students are responsible
  for missed lecture materials and assignments.
• The last day to withdrawal from this class and
  receive a W grade is June 15, 2015.

10
Course Website

• A website will be maintained with a menu to all
  power point slides and assignments
• www.wodwhere.com/cop2700

11
Electronic Device Use and Email Policy

• Cell phones may be turned on but must be set to
  vibrate/quiet mode during class. If you must make
  or receive a call, please exit the classroom.
  Laptops may be used as long as the usage does not
  interrupt other students. The speakers must be
  turned off.
• All email to the instructor must come from your
  Palm Beach State College email account.
• This account goes directly to my phone, so if you
  need to reach me, this is the best bet.
• YOU NEED TO CHECK YOUR EMAIL AT LEAST DAILY TO
  SEE IF I SENT YOU A MESSAGE. PLEASE RESPOND WHEN
  ASKED TO RESPOND!!

12
Ethics

• Students at Palm Beach State College are expected
  to maintain the highest ethical standards.
• Academic dishonesty is considered a serious
  breach of these ethical standards, because it
  interferes with the colleges mission to provide
  a high quality education in which no student
  enjoys unfair advantage over any other.
• Academic dishonesty is also destructive of the
  college community, which is grounded in a system
  of mutual trust and place high value on personal
  integrity and individual responsibility. Harsh
  penalties are associated with academic dishonesty.

13
Cheating???

• There is always a fine line between helping your
  fellow student and cheating. Pointing a student
  in the correct direction, giving advice,
  suggesting other reading or explaining that an
  answer or assignment is wrong is permissible.
  Giving another student the solution to a problem
  or working together on an individual assignment
  will be considered cheating and will be
  disciplined.
• Considered Cheating
• Two (or more) people working together on an
  individual assignment.
• Someone finding out another students ID and
  copying their work.
• Not Considered Cheating
• Comparing assignments after they have been
  independently completed.
• Helping another student with hints or suggestions
  on where to get additional help.

14
Pet Peeves

• Talking in Class
• Cheating
• Not Following Instructions
• Lets Discuss This Since It Is Very Important

15
Any Questions?
16
Your First Assignment

• Your Name
• Your Palm Beach State Email User-Id
• Your current status, class and program (full
  time/part time, 1st Year, 2nd Year)
• A list of any other computer classes you have
  taken.  Please indicate if the class was at PBSC
  and the instructor's name.
• A short paragraph of what you hope to learn from
  this course.
• Any other information you would like to relate to
  me.
• Email me this information to the appropriate
  email Account
• Do This Now - Then Take a Break!!

17
What is a Database

• A large, integrated collection of data.
• Models a real-world enterprise.
• Entities
• a Noun
• (e.g., students, courses)
• Attributes The fields within the Entity
• Relationships
• a Verb that relates two or more Nouns
• Usually answers a question
• (e.g., Which courses is Madonna taking this
  summer?)

18
Some Early Implementations(That are still used
today!!)

• Filing Cabinets
• Card Catalogs
• Entities and Attributes fairly easy to maintain
• Relationships had to be manually maintained
• Suppose we had an author of many books, and the
  author got married and took on a new name
• The Entities involved are Books and Authors
• The Relationship is Who is the Author of a Book
• In the manual world, what has to happen to if
  the author changes her name?

19
Early Computer Implementations

• Cards, Tape and Disk
• Sequential Processing
• Disk was EXPENSIVE
• Used primarily to sort batched input and to hold
  jobs before they were moved to main memory
• Memory was even more EXPENSIVE
• My first main frame computer had 64K Bytes
  total. It was shared by 8 colleges for both
  teaching and administration.

20
Hollerith Card 1890 CensusIBM Fortran Coding
Card
21
External Storage - Tape

• Tracks/Data Blocks/Bits Per Inch (800/1600 when I
  had hair)
• Inter Block Gaps
• Old 9-Track Tapes could hold a max of 370 meg.
• Todays tapes are much smaller with much higher
  capacity and include automatic compression, but
  are used less and less as solid state and disk
  storage becomes as competitively priced.

22
Sequential Processing

• Strict Record structure
• Limited ability to depict all relationships
  between data
• Those that were implemented were fixed
• Usually set up with a base key and record
  types
• Typical Examples
• Inventory
• Accounting
• Payroll

23
Sequential Processing

• Cards or Tapes kept in order by base key, record
  type and record key
• Inventory Example
• Base key might be part number
• Main record would provide basic information on
  the part
• Child records could contain
• Storage information (which Warehouse, how many)
• Restock orders (Vendor, how many)
• Picking Orders (Customer, how many)

24
Sequential Processing
Item Rec Type Qty
110 1 6-Pack Corona
110 2 Whse 1 20
110 2 Whse 2 14
110 3 West Palm Booze 14
110 4 Bradley, Louis 23
110 4 Brewster, Matt 3
25
Sequential File Processing
Main Sequential File
Updated File
Merge
Unsorted Transactions
Sorted Transactions
Sort
26
Transaction Processing

• Requires direct access device
• Requires indexing into information to find
  specific entity
• Allows for more direct ability to form
  relationships between entities

27
Direct Access - Disks

• Sectors (normally 512 Bytes) Tracks Cylinders
• Seek Time Rotational Delay Transfer Time

28
What is a Database Management System (DBMS)

• A software package designed to store and manage
  databases
• Concurrent Access
• Data Independence
• Transaction Logging and Recovery

29
Why Use a DBMS?

• Data independence and efficient access.
• Reduced application development time.
• Data integrity and security.
• Uniform data administration.
• Concurrent access, recovery from crashes.

30
Hierarchical Data Model

• Information Management System (IMS)
• Developed by IBM in Late 60s
• Sabre developed in IMS and still used today
• Tree Structured database (each child can have
  only one parent)

Parts
Salesmen
Orders
Inventory
Payroll
31
Network Data Model

• Charles Bachman
• Integrated Data Store
• Parent-Child relationship
• Children can have multiple parents
• General Electric
• 1969 CODASYL Standard

Parts
Salesmen
Orders
Inventory
Payroll
32
Relational Data Model

• Edgar Codd in 1979
• IBMs San Jose Research lab
• Now major DBMS model used
• Disk space, memory requirements issues at first.
• No strict parent/child relationships. All
  relationships are dynamic (but can be supported
  by primary/foreign keys).

33
Relational Data Model

• Main concept relation, basically a table with
  rows (sometimes called tuples) and columns.
• Every relation has a schema, which describes the
  columns or fields (or attributes) within the
  table.
• Both entities and relationships can be described
  in a relation.

34
Major Relational Players

• Oracle
• Large scale corporation databases
• financials
• inventory
• Microsoft SQL Server
• Small to medium corporation databases
• Integrated tightly with other Microsoft products
• Similar product support as Oracle
• MySQL (now owned by Oracle)
• 1 Open Source database
• used by many social media sites (Facebook,
  Twitter, etc.)

35
Data Definition Language (DDL)

• Used to define the schema and manage the database
  structure
• Create Table
• Alter Table
• Create Index

36
Data Manipulation Language (DML)

• Commands to add, update and delete records in the
  database
• INSERT
• UPDATE
• DELETE
• Commands to query the database
• SELECT

37
RDMS provides ACID Support

• Atomicity All or nothing updates. When a
  transaction starts, either all pieces of that
  transaction complete or nothing completes.
• Consistency Insures that invalid data cannot be
  added to a database (a Book record cannot have an
  invalid Author ID)
• Isolation To the user, her view is such that it
  seems she is on the only user of the system, but
  in reality many users are accessing the database
  simultaneously
• Durability An RDMS provides for logging,
  back-up/restore and other mechanisms to ensure
  data reliability.

38
RDMS Drawbacks

• ACID support requires control by single
  processor to ensure compliance
• As the database grows, so then does the server on
  which it processes.
• For massively large databases where immediate
  updates are not required, the ACID model is
  overly restrictive

39
NoSQL Databases

• To try and provide for access to massively large
  data sets, several companies have developed what
  are named NoSQL (for Not Only SQL) databases
• Several different models using different data
  structures.
• Basics include the splitting of data into a self
  defining database across multiple servers.
• XML of JSON (or other) self defining methods
  employed
• As data grows, more small servers can be added
  instead of having to increase capacity of one
  server
• When multiple servers are used, ACID is going
  to severely slow down the update and retrieval
  processes.
• Each version has its own unique data definition
  and data manipulation languages.

40
Most NoSQL Databases process as BASE vs ACID

• Basically Available Since the database is
  spread over many servers, one server can go down,
  and the database is still accessible (just not
  all data)
• Soft State Database changes over time as
  updates are processed throughout the system.
• Eventual Consistency Ultimately all updates are
  recorded over all pieces of the database, but
  not immediately.

41
What we will be using in class

• Microsoft SQLServer Version 2012
• Near the end of class we will cover the
  differences between SQLServer, Oracle and MySQL

42
The Databases We Will Be Using

• Henry
• Models a multi-location book store that tracks
  books, publishers, authors, inventory and their
  relationships.
• Premier
• Models the orders and inventory for a distributor
  of appliances, housewares, and sporting goods
• Registration
• Models the courses, students, instructors and
  grades at a university

43
Henry Book Store System

• Basic Entities
• Books The basic information about a book
• Authors Information about book authors
• Branch Information about a specific book store
• Publisher Information about book publishers
• Relationships
• A book can have multiple authors
• A book can have only a single publisher
• A branch maintains a count of the number of
  copies of each book that they have in stock.

44
Henrys Old Manual System

• How could a System be implemented without a
  System
• Each store would maintain their own separate list
  of books with an on-hand quantity
• Authors and Publishers would only be maintained
  at the corporate office
• As a store sells a book, the manual on-hand
  quantity would be reduced.
• Many of the relationships required cannot be
  easily supported.

45
Henrys Sequential Processing System

• The central entity in the system would be a Book
• Also included in the sequential system would be
• Author Records
• On-Hand Records
• Branches and Publishers would be maintained in
  separate files
• When a sale happens at a branch, the book sold
  information would be sent to corporate and used
  to update the sequential system.
• Although there is now an overall view of the
  availability of a book, there is still several
  relationships that are not implemented.

46
Henry Book Store Database
47
Registration System

• Basic Entities
• Student Someone taking or has taken classes
• Course Basic information about a course
• Instructor Basic information about a professor
• Relationships
• Students can register for multiple courses
• A course must be scheduled for a semester,
  instructor, location and time before it can be
  added to a student.

48
Registration Database
49
Next Class

• Load our databases
• Go over SQLServer Management Studio
• Introduce the Select statement for single table
  queries
• Well be doing something bad to start with so
  that our queries can be return information on
  both entities and relationships in a single table.

50
Pop Quiz

• WOOHOO!!