emil sekerinski, mcmaster university, winter term 16/17 ...cs1md3/14 databases.pdfa database schema...
Post on 17-May-2020
1 Views
Preview:
TRANSCRIPT
EmilSekerinski,McMasterUniversity,WinterTerm16/17COMPSCI1MD3IntroductiontoProgramming
AnSQLquerygoesintoabar,walksuptotwotablesandasks,"CanIjoinyou?"
Likeprogramminglanguages,databasesallowtoprocessandstoredata.However:¡ dataisstoredindedicatedfilesandloadedasneeded:data
canbemuchlargerthantheavailablememory(retailercatalogue,bankaccounts,personnelrecords);thereisnoneedtoexplicitlyopenfilesandreaddata
¡ datacanbeaccessed"simultaneously"by"clients"¡ dataisatomicallyupdated:dataiseitherstoredornot,but
neverbecomescorrupt,evenincaseoffailure(diskerror,networkdisconnection)
¡ datamustbestructuredinspecificways,e.g.astablesinrelationaldatabases
¡ complexqueriescanbewritteninadedicatedquerylanguage
Dataitemsandtheirrelationshipisstoredintables.Atableisacollectionofrecords(objects,entities)withfields(attributes).
Movie: MovieId Title Genre Rating
101 Casablanca dramaromance PG
102 BacktotheFuture comedyadventure PG
103 Monsters,Inc animationcomedy G
104 FieldofDreams fantasydrama PG
105 Alien sci-fihorror R
106 Unbreakable thriller PG-13
107 X-Men actionsci-fi PG-13
5022 Elisabeth dramaperiod R
5793 LifeofBrian comedy R
7442 12AngryMen drama PG
Adatabaseschemaspecifiesthetypesofthefieldsofeachtable:¡ INTEGER:upto8bytesintegers¡ REAL:8bytefloats¡ TEXT:Unicodestrings¡ BOOLEAN:storedas0and1¡ DATE:storedasnumericvalue
Aprimarykeyisafieldthatuniquelyidentifiesarecord.Thekeyfieldsarespecifiedintheschema.Bydefault,fieldsareTEXT:Movie(MovieIDINTEGERPRIMARYKEY,Title,Genre,Rating)
SQLiteisawidelyusedserverless(databasefilesarestoredlocally)relationaldatabasethatcomespreinstalledorcanbedownloadedatsqlite.orgAllrelationaldatabaseshavesimilarcommands.WeshowtheuseofSQLitefromthecommandlineandwithPython.ForexampleintheMacOSXorLinuxcommandline:$ sqlite3 videostore.dbSQLite version 3.8.5 2014-08-15 22:37:57Enter ".help" for usage hints.sqlite>...sqlite> .quit
create table Table (Column [Type], …)insert into Table values (Expr, …)Allcommandshavetobeterminatedby;create table Movie(MovieId INTEGER PRIMARY KEY, Title, Genre, Rating);
insert into Movie values(101, 'Casablanca', 'drama romance', 'PG');
insert into Movie values(102, 'Back to the Future', 'comedy adventure', 'PG');
insert into Movie values(103, 'Monsters, Inc', 'animation comedy', 'G');
insert into Movie values(101, 'Field of Dreams', 'fantasy drama', 'PG');Error: UNIQUE constraint failed: Movie.MovieId
Supposewewantkeeptrackwhichcustomerrentedwhichvideo:customersandtherentsrelationshipareexpressedastablesCustomer:Rents:
CustomerId Name Address
101 DennisCook 123Broadwalk
102 DougNickle 456ParkPlace
103 RandyWolf 789PacificAvenue
104 AmyYao 321StJamesPlace
105 RobertMwanri 654MarvinGardens
106 DavidCoggin 987CharlesPlace
CustomerId MovieId DateRented DueDate
103 104 3-12-2017 3-13-2017
103 5022 3-28-2017 3-29-2017
105 107 3-28-2017 3-29-2017
create table Customer(CustomerId INTEGER PRIMARY KEY, Name, Address);create table Rents(CustomerId INTEGER, MovieId INTEGER, DateRented DATE, DateDue DATE);
insert into Customer values(101, 'Dennis Cook', '123 Broadwalk');insert into Customer values(102, 'Doug Nickle', '456 Park Place');...
insert into Rents values(103, 104, '3-12-2016', '3-13-2016');insert into Rents values(103, 5022, '3-28-2016', '3-29-2016');insert into Rents values(105, 107, '3-28-2016', '3-29-2016');
update Table set Column = Expr, … [where Expr]delete from Table [where Expr] Forexample:update Movie set Genre = 'thriller drama' where title = 'Unbreakable';
delete from Movie where Rating = 'R';
select Column, … from Table, … where Cond [order by Column, …]Allcolumns(fields)areselectedwith*;theconditionisaBooleanexpressionthatcanhaveconstructsaslike:select * from Movie;select * from Customer;select * from Rents;
select Name, Address from Customer;select * from Movie where Genre like '%comedy%';select * from Movie where Rating = 'PG' order by Title;
select Column, … from Table, … join Table [using(Column)] … where Cond [order by Column, …]Combinedresultofjoiningtables:select * from Customer join Rents using(CustomerId);select * from Customer join Rents using(CustomerId) join Movie using(MovieId);
Allcustomernames,movietitles,andduedates:select Customer.Name, Movie.Title, Rents.DateDue from Customer join Rents using(CustomerId) join Movie using(MovieId);
TitlesofallmoviesofRandyWolfwithduedates:select Movie.Title, Rents.DateDue from Customer join Rents using(CustomerId) join Movie using(MovieId) where Customer.Name = 'Randy Wolf';
Ifacustomerisdeletedwhohasstillvideosrented,thentheRentstablewouldrefertoacustomerthatdoesnotexist,whichisaninconsistencyinthedatabase.Triggerscanautomaticallyensurethiskindofconsistency:create trigger Name delete on Table [when Expr] begin statement; ... endThestatementcanbeanyofinsert,delete,updateandoldcanbeusedtorefertothevaluewhichissupposedtobedeleted,create trigger delete_customer delete on Customer begin delete from Rents where Rents.customerid = old.customerid; end;
delete from Customer where customerid = 103;willnowautomaticallyalsoexecutedelete from Rents where Rents.customerid = 103;Triggerscanalsobecreatedforinsertionandupdatescreate trigger Name insert on Table [when Expr] begin statement; ... endcreate trigger Name update on Table [when Expr] begin statement; ... endThestatementofaninsertionmayusenewtorefertothevaluetobeinserted,thestatementofanupdatemayuseoldandnewtorefertheoldandnewvalue.
SQLisastandardizedlanguagethatwasfirstproposedin1974forCodd'srelationaldatamodelfrom1970.Besidesqueries(select)italsosupportsmanipulation(create,insert,update,delete,…)transactions(begin,commit,rollback),authorization,programmingconstructsSQLismoregeneralthantheexamplessuggest,e.g.theresultofanSQLquerymaybetheinputofanotherone.Alloftoday'srelationaldatabasessupportSQL,withvariations.Morerecently,also"NoSQL"databasesfordifferentlystructureddataandcloudstoragearebecomingpopular.
Programscanstoredatainfileswiththebuild-infunctions;alternatively,programscanstoredatainadatabase.PythonhasalibrarymodulethroughwhichanSQLite3databasecanbeaccessed:import sqlite3db = sqlite3.connect('…/videostore.db')...db.close()Dataisconvertedasfollows:null ↔︎ Noneinteger ↔︎ intreal ↔︎ floattext ↔︎ str
AllSQLstatementsarepassedasstringtothedatabase.AcursorisneededtoiterateovertheresultsofanSQLquery:c = db.cursor()c.execute('select * from Customer order by Name')print(c.fetchone())Ifthereisnoresult,Noneisreturned.SeveralSQLrowscanberetrievedasalistoftuplesc.execute('select * from Customer order by Name')print(c.fetchall()) Acursorcanusedasaniterator:for row in c.execute('select * from Customer'): print(row)
SQLdatamanipulationstatementsdonothaveaneffectrightaway,onlyaftercommit.c.execute("insert into Customer values(103, 'Randy Wolf', '789 Pacific Avenue')")db.commit()
c.execute("delete from Customer where CustomerId = 103")db.commit()SinceconstructingSQLstringsistedious,aspecialformatinsupported,inwhich?inSQLarereplaceddef insert_customer(db, customerid, name, address): c = db.cursor() c.execute("insert into Customer values(?, ?, ?)", \ (customerid, name, address)) db.commit()
insert_customer(db, 103, 'Randy Wolf', \ '789 Pacific Avenue')
top related