This refers you to my website, and the course page thereof.
See links below.
Database software provides a solution to a recuring problem. Why reinvent the wheel?
We start with the high-level modelling of data. Entities have attributes, such as people have a name, an email address, a birthday, etc. We can think of a SET of people. a Relationship connects entities. People can have relationships, with each other, and with pets.
A relation is a table of rows and columns. Or think of a set of tuples. We can implement an E/R model in this simpler model, and use algebra to get whatever result we want...
Manipulating sets. Algebra has precise rules, and they work! Oops, the ending is silly, see...
The last example worked, but threw away the work in the end. I correct the example, and drw the RA operations as a tree, leading to the desired result.
When we start doing labs, there is some software you might need to download. The first 2 are only for windows users. Linux and Mac-OS, use scp and ssh from a terminal window.
Shahn Nadeau of ITS has made a video of how to download these. He also sent us the links to the sources he mentions.
The language of relational databases. SELECT queries and data manipulation.
The sun came out, and caused a glare on the whiteboard. Here is what you probably can't read:
SELECT name, email, course
FROM student S, enrol E
WHERE major = 'CS'
AND S.email = E.email
ORDER BY name;
I mentioned that τ means SORT in relational algebra, this is specified in a SELECT statement
with ORDER BY ..., which follows the WHERE clause (and then I forgot to write it)UPDATE student SET major='CS' WHERE email = 'jbond20'; DELETE from enrol WHERE email = 'jbond20 AND course = 'MAT100';Sorry for the glare and mysterious clacking.
Model of the ships database, and SQL demonstation
More SQL with narration, using the Ships database. The terminal screen is shown. I intended to copy queries and results to a text file, but forgot. (There is some redundency with the previous)
See me Create table, alter table, inserting and modifying data. Where is George? You can insert yourself, but lack update permission.
What to expect for the Midterm, thinking about your database project (assignment 4) problems with concurrent access, and the need for transactions. Last year the lab to "demonstrate" this went crazy. We won't try that remotely! Coming soon, a lecture on transactions.
Group statements that need to be done together into a transaction, or else you might lose a lot of money while defogging your glasses. Transactions are ACID
Don't let the public have SQL access to a database. You need applications that handle transactions safely. Names like o'hara cause errors, and hackers can exploit careless programs. You will be doing applications soon. C++ strings are safer than C strings, which are subject to buffer overflow.
Using defaults and the types serial & money keep much transaction processing on the server side, simplifying the applications you will be creating. Rules can cause other sql statements to be done. Functions can raise notices when something happens, a trigger.
How to order from the fruit stand. I discover my web app doesn't show totals! Next week A good command line application...
I share my screen and look at and execute fruitstand (as described last week ) command line applications written in both php and C++. Each does the same queries, insert and then select the information for one order for buying fruit.See how the SQL is accessed from these languages.
Describing some features of Python language and Database interface. The 2 apps in the course directory are proflist.py (a select query) and fruitstand.py --same logic as the other fruitstand implementations.
Caution: When companies merge, each has working databases. Care must be taken in sharing differently-structured data. The Phoenix payroll system was created at huge expense to do the payroll for the entire federal government. It has continuing problems of over- and under-paying employees. It replaced departmental systems that functioned well.