CSCI 242 Advanced Database

1. Introduction CSCI 242Advanced Database

2. How the course works • Homework • Project • Exams • Grades • prerequisite • CSCI 6441: Mandatory prerequisite • Take the prereq or get permission to take the course • Goals of the course • Advanced topics • Topics that are often not understood • Realistic experience • Workload • Heavy workload from the beginning • Workload gets much heavier when project starts • Relational Principles • Why relational, why it matters Agenda

3. Weekly assignments • Assignments intended to be challenging and to make an important point • Assignments to be submitted by email to homework@csci6442.org • No attachments • Assignments due at the start of class • Late assignments are not accepted Homework

4. One project will involve the entire class • Work will be performed in functional teams • Every student must produce programs, will be graded on personally produced results • You will need to program using MySQL, PHP, and JavaScript; if you don’t know them start learning • You need to already have programming skills to complete this course Project

5. The project will be a questionnaire generator and evaluator of results • One team will build the questionnaire builder and administrator • Another team will build the data analysis • Both will be built as WordPress plugins Project

6. At the end of the project, each student will have access to all code written by the project • Any student (and myself) are free to do anything desired with the project code • You will receive an email asking you to agree to these stipulations • If you have a problem with this approach, please speak up after class tonight Project Rights

7. Midterm and final • Midterm will be closed book, in class • Midterm date will not change • Plan your schedule now: • Be here for the midterm, no makeup exams • Be here for the final, no makeup exams • Midterm will test your ability to work with concepts discussed in lecture and covered by homework Exams

8. A: Good quality graduate work, only minor issues with correctness • B: Acceptable graduate work, one or more major issues • C: Not acceptable graduate work, several serious issues • F: Does not show basic understanding Grades

9. CSCI 6441 is a mandatory prerequisite • Take it before this course • If you think you know the material, you need to explain it and get permission • First assignment is intended to clear this up Prerequisites

10. Misunderstood topics • Normalization • Database design • Performance • SQL • Advanced topics • Time in databases • Translucency • Performance • Realistic experience • Realistic team size • Accountability • Emerging requirements • Current Developments • Big data • NOSQL • Cloud Computing Goals

11. This course is for advanced students who want to learn a lot and want to work hard • If you’re not in a position to work hard, want to “slide by,” you are in the wrong course • If you don’t know how to program, don’t want to learn PHP, MySQL and Flex (on your own), this is not the course for you • But if you do want to be the database guru on a project at work, then stay in this course! Workload

12. Earlier database systems: hierarchies, networks as data models • Relationships represented as physical connections • Structure of relationship imbedded in applications • Relational: independent table as data model • Relationships represented by equal values • Structure of relationships invisible to applications Relational Principles

13. Relational Database: a set of relations Relational Database

14. Relation: a set of ordered pairs • Ordered pair: a pair of values, such that interchanging the two values changes the meaning • That is, <a,b>=<b,a> iff a=b and b=a • Specifying a relation by enumeration: R={<a,b>,<c,d>,<e,f>} • This is a relation consisting of three ordered pairs. Relation

15. Ordered pairs can model more than two values through nesting: • <a, b, c> == <<a,b>, c> • <a, b, c, d> == <<a,b>, c, d> • And so on • This extends the ordered pair so that it can model a tuple of any length • Now a relation starts to look like our notion of a file, with each tuple corresponding to our notion of a record Relation and File

16. Relation is a set of ordered pairs (modeling a set of tuples), so: • 1. exchanging order of values within a tuple changes the meaning of the tuple • 2. exchanging the order of tuples within a relation does not change the meaning of the tuple • 3. duplicate tuples are not allowed The Definition

17. Now we build a database as a collection of independent relations, each describing instances of a single entity type • For example: • Employee (employee#, job, salary, department) • Department (department#, departmentname, location) (this is called schema notation) Data Modeling

18. We need a way to insert data into the database, retrieve data from the database, and changes values that are stored in the database • We define a data language that can be used from any programming language to do that • The data language (SQL) has a lot of power and can save a lot of programming work if you understand it • You’ll have a brief chance to learn more about SQL in this course Data Language

19. Database courses talk about normalization • Students usually don’t learn more than memorizing definitions • We will talk about Roberts’s Rules, plain English rules that give you a highly normalized database • Then we will talk about the normalization rules and what they mean in English • You will have the chance to really understand how to do this Normalization

20. Time in databases is a complex issue • There’s the time something happened and the time it’s entered into the database • And there’s also the effective time, which may differ from those two times • And there’s the need to capture a history of previous values and roll back to it • We’ll examine all of these cases of time Time

21. Typically the GRANT statement is used to give access to a database • A DBA enters the statement, and a user has access • But that’s not good enough if there may be thousands of people on the Web using a database • We’ll study translucency, a way to provide access control without GRANT statement Translucency

22. Professional standards, like the work environment, will be followed: • Arrive at class on time • Submit homework on time • Limit answers to 50 words Responsibility

23. A class Web site has been established • Everything about the class is on it • Please read it • When changes are made to the site a note will be sent to the class email list Class Web Site

24. A class email list has been established • If you got the email sent today, then you’re on it • You can follow instructions on the Web site to enroll a different email address EMAIL list