COP 2700 – Data Structures (SQL)

1. COP 2700 – Data Structures (SQL) Lecture 1 – May 18, 2015

2. Introductions • Bradley’s Contact Info • bradleyl@palmbeachstate.edu • 407-925-8751 • Bradley’s 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 • I’m 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. Don’t 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 college’s 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 student’s 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 • Let’s 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. • Today’s 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

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 60’s • 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 • IBM’s 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 it’s 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. Henry’s 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. Henry’s 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 • We’ll 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!!