Databaser & databasdesign

Databaser & databasdesign SQL

Föreläsningens innehåll SQL: Syfte? SQL: Att skriva SQL SQL: Data manipulation SQL: Data definition

Först liten repetition av några relationella begrepp (Date, 2000, p.111)

SQL: Syfte? • Skapa databaser och relationella strukturer • Utföra diverse datahanteringsuppgifter • Att tillhandahålla möjlighet att ställa både enkla och komplexa frågor • Indelas i en DDL-del och en DML-del

SQL: Att skriva SQL • Mer läsbar SQL-kod: • Varje sats börjar med en ny rad • Början av varje rad ska ligga i linje med andra satser • Då en sats har flera delar, bör varje del börja på en ny indragen rad • Extended Backus Naur Form (BNF): • VERSALER - reserverade ord • Gemener - användardefinerade ord • a|b - val mellan olika alternativ • {a} – element som krävs • [a] – valbart element • (...) – valbar iteration

SQL: Ett exempel CREATE TABLE n_kund ( kundnummer INT NOT NULL, namn VARCHAR(50) NOT NULL, personnummer CHAR(11), PRIMARY KEY (kundnummer));

SQL: Data manipulation Fyra huvudsakliga typer av frågor: • SELECT – presentera data ifrån databasen • INSERT – lägga till nya data i en tabell • UPDATE – uppdatera innehåll i en tabell • DELETE – ta bort data från en tabell

SQL bygger på relationsalgebra: SQL-DML

SQL/DML: SELECT-sats SELECT [DISTINCT | ALL] {* | [columnExpression [AS newName]] [,...] } FROM TableName [alias] [, ...] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList]

SQL-DML: sökning i tabell • Exempel: Vi vill hämta alla rader i tabellen artikel. • SELECT * FROM artikel; • Exempel: Vi vill hämta enbart kolumnerna artnr och artnamn från tabellen artikel. • SELECT artnr, artnamn FROM artikel; • Exempel: Vi vill hämta alla artiklar som kostar mer än 1000kr. • SELECT * FROM artikel WHERE pris>1000; operatorbetydelseexempel = lika med artnr=’10001’ <> skilt från artnr<>”10002” > större än pris>1000 < mindre än pris<20 >= större än eller lika med antal >=50 <= mindre än eller lika med pris<=20

SQL-DML • Det finns även inbyggda funktioner • Exempel: Hur många artiklar har vi? • SELECT COUNT (*) FROM artikel;

SQL-DML • UPDATE (uppdatera befintliga data) • Exempel: Vi vill ändra pris till 33kr och lagerantal till 10 för artkel 10001. • UPDATE artikel SET pris=33.00, lagerantal=10 WHERE atrnr=’10001’; • INSERT (lägga till en ny rad) • Exempel: Vi vill lägga till en ny artikel med artikelnummer 10006 och namnet SuperComputer. De andra fälten lämnas i detta fall blanka. • INSERT INTO artikel (artnr, artnamn) VALUES (’10006’, ’SuperComputer’);

SQL-DML • DELETE (ta bort rad/rader) • Exempel: Vi vill ta bort alla rader i tabellen artikel. OBS! Anges inga villkor tas ALLA rader bort!!! • DELETE FROM artikel; • Exempel: Vi vill ta bort artikeln 10006. • DELETE FROM artikel WHERE artnr=’10006’;

En liten övning i SQL • a) Vilka leverantörer finns det? (Visa namn) • b) Vilka artiklar är beställda från leverantörer?(Visa antnr och antal) • c) I vilka orter finns våra leverantörer?(Obs! Ej dubletter!) Segerlund, M. & Stridsman, F. (1998). SQL-introduktion, p29. Lysekil: Pontes.

SQL: DDL skapa en tabell • Exempel: Skapa tabellen artikel (artnr, artnamn, lagerantal, bestpunkt, pris). Det finns olika typer av dataformat som används beroende på vad som ska lagras, tex CHAR(n) – n tecken, INTEGER – ett heltal eller DECIMAL(n,m) – ett decimaltal med n siffror och m decimaler. Dessa format kan variera beroende på vilken databashanterare vi arbetar mot. NOT NULL innebär att ett värde måste matas in, tex för ett primärnyckelfält. • CREATE TABLE artikel (artnr CHAR(5) NOT NULL, artnamn CHAR(15) NOT NULL, lagerantal INTEGER, bestpunkt INTEGER, pris DECIMAL(5,2));

SQL-DDL • Utöka tabeller • Exempel: Vi vill lägga till en enhetskolumn till tabellen artikel. • ALTER TABLE artikel ADD (enhet CHAR(3)); • Ta bort en tabell • Exempel: Vi vill ta bort tabellen artikel. • DROP TABLE artikel; • Skapa index • att skapa en unik nyckel i en tabell • att göra sökningar snabbare • Exempel: Vi vill göra artnr till unikt index. • CREATE UNIQUE INDEX artindex ON artikel(artnr);

Open DB Connectivity (ODBC) • är ett funktionsbibliotek utformat för att tillhandahålla ett gemensamt programmeringsgränssnitt (API: Applikation Programming Interface) mot det underliggande databassystemet. • Vi kan nå databaser över nätverket med hjälp av ODBC. • Det unika med ODBC är att ingen leverantörsspecifik kod behövs. Vi kan tex använda samma kod för frågor till en tabell i MS SQL-server, Informix eller i MySQL... • ODBC gör programmen mer portabla mellan olika system, men långsammare än när man använder systemspecifika DB bibliotek. Program anropar ODBCs funktioner Drivrutins-hanterare läser in ODBC-drivrutin ODBC-anrop sänder SQL-frågor, returnerar resultat... Datakälla: underliggande DBMS

Database Languages • Data Definition Language (DDL) • Allows the DBA or user to describe and name entities, attributes, and relationships required for the application • plus any associated integrity and security constraints. • Data Manipulation Language (DML) • Provides basic data manipulation operations on data held in the database. • Procedural DML • allows user to tell system exactly how to manipulate data. • Non-Procedural DML • allows user to state what data is needed rather than how it is to be retrieved. • Fourth Generation Languages (4GLs)

Constraint on relationships • Cascade delete • The relation belowtry todescribe the structureof an order. Can an orderheadexistwithout a row? • If youdelete a certainrow in orderhead…whathappens? • Cascade update • If youupdateoredernr in orderhead…whathappens? Orderhead Orderrow 1 * Ordernr Partnr Quantity Ordernr Date custnr

What is a storedprocedure? • Howitsworking? • SQL server has a languagecalledtransact SQL and itsusedtocreatemodulesoflogicdatabase actions. • It is stored in databasewith a nameto call whenyouneed it. • An exampleofexecuting a storedproceduretocreate a new customer:EXEC sp_newcustomer (’23’,’Jesper’, ’Hakeröd’,…) • Morepractise on this in laboratorywork 2.

Exampleof a storedprocedure -- ============================================= -- Author: <Jesper> -- Create date: <2009-10-10> -- Description: <Insert a new customer> -- ============================================= CREATE PROCEDURE [dbo].[uspNewCustomer] (@CustomerID as bigint, @firstname as nvarchar(50), @surename as nvarchar(50), @address as nvarchar(50), @cellular as nvarchar(50), @email as nvarchar(50), @zipcode as nvarchar(10)) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interferingwith SELECT statements. SET NOCOUNT ON; -- Insertionof the customer INSERT INTO CUSTOMER (CustomerID, firstname, surename, address, cellular, email, zipcode) VALUES (@CustomerID, @firstname, @surename, @address, @cellular, @email, @zipcode); END

Kommer ni ihåg begreppen? • Supernyckel = Ett eller flera attribut som ensamt eller sammansatt unikt identifierar en tuple i en relation. • Kandidatnyckel = En supernyckel där det inte kan tas bort något attribut som ingår i supernyckeln, utan att man mister den unika identifieringen av en tuple. • Kompositnyckel = En nyckel som består av flera attribut kallas för sammansatt- eller kompositnyckel. • Primärnyckel = En primärnyckel är en vald kandidatnyckel som unikt kan identifiera en tuple. • Alternativ nyckel = kandidatnycklar förvandlas till alternativa nycklar när primärnyckeln valts. • Främmande nyckel = Ett attribut eller en samling attribut i en relation som matchar en kandidatnyckel i en annan relation.

Normaliseringsformer repetition • Onormaliserat – UNF • En tabell som innehåller repeterade grupper. • Första Normalform - 1NF • En tabell är i 1NF om det i varje tuple bara finns ett värde för varje attribut. Välj även ut en primärnyckel. • Andra normalform - 2NF • En tabell är i 2NF om alla icke nyckelattribut är funktionellt beroende av hela primärnyckeln. • Tredje normalform - 3NF • En tabell är i 3NF om alla icke nyckelattribut inte är transitivt beroende av primärnyckeln.

Data dictionary - entities

Data dictionary - attributes

Databaser & databasdesign