L01.pdf

IT5008 Database Design & Programming L01: Creating and Populating IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 1 / 45 Case Study IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 2 / 45 Case Study ❱ Requirement Requirement Design Game Store Our company, Apasaja Pte Ltd, has been commissioned to develop an application to manage the data of an online app store. We want to store several items of information about our customers such as their first name, last name, date of birth, e-mail, date and country of registration to our online sales service and the customer identifier that they have chosen. We also want to manage the list of our products, games, their name, their version, and their price. The price is fixed for each version of each game. Finally, our customers buy and download games. We record which version of which game each customer has downloaded. It is not essential to keep the download date for this application. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 3 / 45 Case Study Requirement Design ❱ Design Entity-Relationship Diagram IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 4 / 45 DBMS IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 5 / 45 DBMS Just some history… relation is the key. ❱ RDBMS RDBMS Systems Architecture Database Management Systems Definition Most popular database management systems since the 1970’s are relational. They implement the relational model of Edgar F. Codd, a simple and rigourous data model based on relations implemented as tables. They support a language for the definition, manipulation, query, and control, which intersects the international standard for the SQL language. "Future users of large data banks must be protected from having to know how the data is organized in the machine." Edgar F. Codd ("A Relational Model for Large Shared Data Banks") IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 6 / 45 DBMS RDBMS Systems ❱ Systems Architecture Popular Systems SQLite is a little special IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 7 / 45 DBMS RDBMS Architecture Systems ❱ Architecture Client-Server Client-Server Three-Tier Client-Server Architecture Most popular relational database management systems since the 1970’s have a client-server architecture and manage and control distributed and concurrent access to, usually large amounts of, persistent data on secondary storage, disk. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 8 / 45 DBMS RDBMS Architecture Systems ❱ Architecture Three-Tier Client-Server Three-Tier Three-Tier Architecture Most modern database applications have a three-tier architecture. A three-tier architecture is a client-server software architecture that divides applications logically and physically into three distinct tiers: the presentation tier or clients, the business logic tier or application server, and the data tier or database server. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 9 / 45 Logical Design IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 10 / 45 Logical Design ❱ Model Model Design Creation Relational Model Constraints Recreation Relation Updates Good Practice In theory, the relational model proposes to organise data in relations. Relations have a name and attributes. Attributes have a name. Mathematically, relations are subsets of the Cartesian product of the domains of their attributes. Domains are extensions of types. first_name last_name email dob since customerid country Johnny Gilbert [email protected] 1995-06-19 2023-07-15 JohnnyG89 Malaysia Deborah Ruiz [email protected] 1990-08-01 2024-04-17 Deborah84 Singapore... IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 11 / 45 Logical Design ❱ Model Model Design Creation Relational Model Constraints Recreation Relation Updates Good Practice In practice, the relational database management systems organise data in tables. Tables have a name. Tables are multi-sets (not lists) of rows or records. Rows or records have fields corresponding to the columns of the table. Columns or fields have a name. Columns or fields also have an implicit position indicated by the order in the corresponding creation statement. Columns or fields have a domain which is a type to the extension of which is added the possibility of a NULL value. ✱ We shall discuss in a subsequent lecture the syntax, semantics, and behavior of NULL values and of the constructs that are used to manipulate them. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 12 / 45 Logical Design Model Design ❱ Design Creation Logical Design Constraints Recreation Database Logical Design Updates Good Practice Logical design is the activity consisting in choosing the appropriate schema for the database application. It consists mainly the number of tables, the names of the tables, the number of columns each table should have and the names and domains of the columns. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 13 / 45 Logical Design Model Creation Design ❱ Creation Basic Table Creation Basic Decomposed CREATE TABLE downloads ( Note Constraints first_name VARCHAR(64), Recreation The choice of the number of columns and of their last_name VARCHAR(64), Updates domains implicitly imposes structural constraints. Good Practice email VARCHAR(64), dob DATE, since DATE, customerid VARCHAR(16), country VARCHAR(16), name VARCHAR(32), name of game version CHAR(3), price NUMERIC ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 14 / 45 Logical Design Model Creation Design ❱ Creation Decomposed Table Creation Basic Decomposed CREATE TABLE customers ( CREATE TABLE games ( Constraints first_name VARCHAR(64), name VARCHAR(32), Recreation last_name VARCHAR(64), version CHAR(3), Updates Good Practice email VARCHAR(64), price NUMERIC dob DATE, ); downloads since DATE, CREATE TABLE games ( customerid VARCHAR(16), customerid VARCHAR(16), country VARCHAR(16) name VARCHAR(32), ); version CHAR(3) ); We opt for a schema with three tables above with the indicated columns and their respective domains (e.g., VARCHAR(64), DATE, CHAR(3), NUMERIC, etc)✱. ✱ See: https://www.postgresql.org/docs/current/datatype.html IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 15 / 45 Logical Design Model Constraints Design Creation Integrity ❱ Constraints Integrity Integrity Constraints In Practice Support A consistent state of the database is a state which complies with the business rules as Recreation defined by the structural constraints and the integrity constraints in the schema. Updates Good Practice Properties Atomicity Consistency Isolation Durability example of transfer money，转⼊转出不是同时进⾏的 IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 16 / 45 Logical Design Model Constraints Design Creation In Practice ❱ Constraints Integrity Transaction In Practice Support In practice, there are different ways to specify the scope of transactions in SQL or application Recreation code. One of them is to use blocks with keywords such as BEGIN, END or COMMIT, ABORT and Updates ROLLBACK. Good Practice Deferred In most systems, unfortunately, integrity constraints are immediate (i.e. they are checked after each operation). It is preferable to set all integrity constraints to be deferred (i.e. they are checked after the end of transactions). Unfortunately again, most systems, like PostgreSQL, do only allow certain constraints to be deferred. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 17 / 45 Logical Design Model Constraints Design Creation Support ❱ Constraints Integrity Integrity Constraints In Practice Support SQL Support five kinds of integrity constraints: Recreation NOT NULL Updates PRIMARY KEY Good Practice UNIQUE FOREIGN KEY CHECK IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 18 / 45 Logical Design Model Recreation Design Creation Delete Constraints ❱ Recreation DELETE FROM Delete Primary Key Not NULL DELETE FROM customers; Unique Foreign Key Check DELETE deletes the content of the table but not its definition. Updates Good Practice DROP TABLE DROP TABLE customers; DROP deletes the content and the definition of the table. ✱ We recommend https://www.w3schools.com/sql/ for details of syntax and behavior of standard SQL construct. For PostgreSQL specific behavior, see https://www.postgresql.org/docs/ instead. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 19 / 45 Logical Design Model Recreation Design Creation Primary Key Constraints ❱ Recreation Definition Delete Primary Key A primary key is a set of columns that uniquely identifies a record in the table. Each table Not NULL has at most one primary key. Unique Foreign Key Check Updates Note Good Practice The primary key can be one column or a combination of columns. An instance of the table cannot have two records with the same value or combination of values in the primary key columns and no primary key column cannot contain a null value✱. ✱ This is not implemented in all database management systems. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 20 / 45 Logical Design can be uppercase or lowercase but do not mix, Model Recreation and the primary key should be underlined Design Creation Primary Key Constraints ❱ Recreation Column Constraint Table Constraint Delete CREATE TABLE customers ( CREATE TABLE customers ( Primary Key Not NULL first_name VARCHAR(64), first_name VARCHAR(64), Unique last_name VARCHAR(64), last_name VARCHAR(64), Foreign Key Check email VARCHAR(64), email VARCHAR(64), Updates dob DATE, dob DATE, Good Practice since DATE, since DATE, customerid VARCHAR(16) customerid VARCHAR(16), PRIMARY KEY, -- applies to the row country VARCHAR(16), country VARCHAR(16) PRIMARY KEY customerid -- after row ); ); customers(first_name, last_name, email, dob, since, customerid, country) IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 21 / 45 Logical Design Model Recreation Design Creation Primary Key Constraints ❱ Recreation Definition INSERT INTO Delete CREATE TABLE customers ( INSERT INTO customers VALUES ( Primary Key Not NULL first_name VARCHAR(64), 'Carole', Unique last_name VARCHAR(64), 'Yoga', Foreign Key Check email VARCHAR(64), '[email protected]', Updates dob DATE, '2002-08-01', Good Practice since DATE, '2024-08-09', customerid VARCHAR(16), 'Carole89', country VARCHAR(16), 'France' PRIMARY KEY customerid -- after row ); ); Try inserting another one with the same customerid, we will get an error. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 22 / 45 Logical Design Model Recreation Design Creation Primary Key Constraints ❱ Recreation Composite Delete Primary Key CREATE TABLE games ( Note Not NULL name VARCHAR(32), Unique Composite primary key is declared as table version CHAR(3), Foreign Key constraint. Each column composing the Check price NUMERIC, Updates primary key is said to be a prime attribute. PRIMARY KEY (name, version) Good Practice ); games(name, version, price) IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 23 / 45 Logical Design Model Recreation Design Creation Primary Key Constraints ❱ Recreation Definition Failure Delete CREATE TABLE games ( INSERT INTO games VALUES Primary Key Not NULL name VARCHAR(32), ('Aerified', '1.0', 5), Unique version CHAR(3), ('Aerified', '1.0', 6); Foreign Key Check price NUMERIC, Updates Good Practice PRIMARY KEY (name, version) Success ); INSERT INTO games VALUES ('Aerified', '1.0', 5), ('Aerified', '2.0', 6), ('Verified', '1.0', 7); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 24 / 45 Logical Design Model Recreation Design Creation Not NULL Constraints ❱ Recreation Code Delete Primary Key CREATE TABLE games ( Note Not NULL name VARCHAR(32), Unique A NOT NULL constraint guarantees that no version CHAR(3), Foreign Key value of the corresponding field in any Check price NUMERIC NOT NULL, Updates record of the table can be set to NULL. A PRIMARY KEY (name, version) Good Practice NOT NULL constraint is always declared as ); a column constraint. When it is explicit, it is declared with the keyword NOT NULL. In the example on the right, the price of a game can never be NULL. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 25 / 45 Logical Design Model Recreation Design Creation Not NULL Constraints ❱ Recreation Definition Implicit NULL Delete CREATE TABLE games ( INSERT INTO games (name, version) Primary Key Not NULL name VARCHAR(32), VALUES ('Aerified2', '1.0'); Unique version CHAR(3), Foreign Key Check price NUMERIC NOT NULL, Explicit NULL Updates PRIMARY KEY (name, version) Good Practice INSERT INTO games VALUES ); ('Aerified2', '1.0', NULL); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 26 / 45 Logical Design Model Recreation Design Creation Not NULL Constraints ❱ Recreation Default Value Success Delete CREATE TABLE games ( INSERT INTO games (name, version) Primary Key Not NULL name VARCHAR(32), VALUES ('Aerified2', '1.0'); Unique version CHAR(3), Foreign Key Check price NUMERIC NOT NULL Failure Updates DEFAULT 1.00, Good Practice INSERT INTO games VALUES PRIMARY KEY (name, version) ('Aerified2', '1.0', NULL); ); * do some reserch: double float question: 0.1+0.2=? there is a answer with it and with a website+answer.com IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 27 / 45 Logical Design Model Recreation Design Creation Unique Constraints ❱ Recreation Code Delete Primary Key CREATE TABLE customers ( Note Not NULL first_name VARCHAR(64), Unique A unique constraint on a column or a last_name VARCHAR(64), Foreign Key combination of columns guarantees the Check email VARCHAR(64) UNIQUE, Updates table cannot contain two records with the dob DATE, Good Practice same value in the corresponding column since DATE, or combination of columns. customerid VARCHAR(16), country VARCHAR(16), UNIQUE (first_name, last_name) SQL Standard ); According to SQL standard (and in most system), PRIMARY KEY is equivalent to UNIQUE and NOT NULL✱. ✱ There are differences in side-effect: foreign-key, index, deferrability, etc. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 28 / 45 Logical Design Model Recreation Design Creation Foreign Key Constraints ❱ Recreation Codes Delete CREATE TABLE downloads ( CREATE TABLE customers ( Primary Key customerid VARCHAR(16) first_name VARCHAR(64), Not NULL Unique REFERENCES customers (customerid), last_name VARCHAR(64), Foreign Key name VARCHAR(32), email VARCHAR(64), Check Updates version CHAR(3), dob DATE, Good Practice FOREIGN KEY (name, version) since DATE, REFERENCES games (name, version) customerid VARCHAR(16) PRIMARY KEY, ); country VARCHAR(16) ); CREATE TABLE games ( Referenced column required to be primary name VARCHAR(32), key for portability. version CHAR(3), price NUMERIC, PRIMARY KEY (name, version) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 29 / 45 Logical Design Model Recreation Design Creation Foreign Key Constraints ❱ Recreation Definition Not In Customers Delete CREATE TABLE downloads ( INSERT INTO downloads VALUES Primary Key Not NULL customerid VARCHAR(16) ('Adam1983', 'Aerified', '1.0'); Unique REFERENCES customers (customerid), Foreign Key Check name VARCHAR(32), Not In Games Updates version CHAR(3), Good Practice INSERT INTO downloads VALUES FOREIGN KEY (name, version) ('Carole89', 'Aerified', '1.1'); REFERENCES games (name, version) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 30 / 45 Logical Design Model Recreation Design Creation Foreign Key Constraints ❱ Recreation Definition NULL Customers Delete CREATE TABLE downloads ( INSERT INTO downloads VALUES Primary Key Not NULL customerid VARCHAR(16) (NULL, 'Aerified', '1.0'); Unique REFERENCES customers (customerid), Foreign Key Check name VARCHAR(32), NULL Games Updates version CHAR(3), Good Practice INSERT INTO downloads VALUES FOREIGN KEY (name, version) ('Carole89', NULL, '1.1'); REFERENCES games (name, version) ); corner case with NULL IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 31 / 45 Logical Design Model Recreation Design Creation Foreign Key Constraints ❱ Recreation Definition Prevent Deletion Delete CREATE TABLE downloads ( DELETE FROM customers Primary Key Not NULL customerid VARCHAR(16) WHERE country='France'; Unique REFERENCES customers (customerid), Foreign Key Check name VARCHAR(32), Updates version CHAR(3), Cannot delete the French customers Good Practice FOREIGN KEY (name, version) because some of them have downloaded some games. REFERENCES games (name, version) ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 32 / 45 Logical Design Model Recreation Design Creation Check Constraints ❱ Recreation Positive Price Delete Primary Key CREATE TABLE games ( Note Not NULL name VARCHAR(32), Unique A check constraint enforces any other version CHAR(3), Foreign Key condition that can be expressed in SQL. A Check price NUMERIC NOT NULL Updates check constraint is declared as a row or a CHECK (price > 0), Good Practice table constraint with the CHECK keyword PRIMARY KEY (name, version) followed by the SQL condition in ); parenthesis. If the condition involves more than one column, it should be a table constraint. In the example on the right, the price of game can not be zero or negative. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 33 / 45 Logical Design Model Recreation Design Creation Check Constraints ❱ Recreation Definition Prevent Update Delete CREATE TABLE games ( UPDATE games Primary Key Not NULL name VARCHAR(32), SET price = price - 5.5; Unique version CHAR(3), Foreign Key Check price NUMERIC NOT NULL Updates CHECK (price > 0), Discounting all the prices by $5.5 creates Good Practice PRIMARY KEY (name, version) negative prices. This operation will be aborted and rolled back. ); IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 34 / 45 Logical Design Model Recreation Design Creation Check Constraints ❱ Recreation Note Delete Primary Key The SQL standard caters for very expression CHECK constraints. Not NULL Unique Foreign Key In practice, vendors only offer check constraints that are limited to very simple row and table Check checks. PostgreSQL CHECK is limited to boolean expression involving one row and one table. Updates Good Practice Vendors usually advocate the use of triggers and stored procedures. But these are notoriously difficult and error-prone. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 35 / 45 Logical Design Model Updates Design Creation Motivation Constraints Recreation Definition Instances ❱ Updates downloads CREATE TABLE downloads ( Motivation Action customerid VARCHAR(16) name version customerid Good Practice REFERENCES customers (customerid), Skype 1.0 tom1999 name VARCHAR(32), version CHAR(3), Comfort 1.1 john88 FOREIGN KEY (name, version) Skype 2.0 tom1999 REFERENCES games (name, version) customers ); customerid email... What happened when things change? tom1999 [email protected]... john88 [email protected]... walnuts [email protected]... IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 36 / 45 Logical Design Model Updates Design Creation Action Constraints Recreation Cascade ❱ Updates CREATE TABLE downloads ( Note Motivation Action customerid VARCHAR(16) ON UPDATE/DELETE with the option Good Practice REFERENCES customers (customerid) CASCADE propagate the update or deletion. ON UPDATE CASCADE ON DELETE CASCADE, Other options: name VARCHAR(32), NO ACTION version CHAR(3), SET DEFAULT FOREIGN KEY (name, version) SET NULL REFERENCES games (name, version) ON UPDATE CASCADE May cause chain reaction. ON DELETE CASCADE ); ✱ For more powerful generalization, we have triggers. IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 37 / 45 Logical Design Model Good Practice Design Creation Not NULL Cascade Constraints Recreation It is generally a good idea to constrain all Think carefully about which foreign keys Updates attributes to be not NULL unless there is a should be subject to cascade. ❱ Good Practice good design or tuning reason for not doing so. Defer Play the Devil's Advocate It is generally a good idea to defer all the Given the complete schema for the three constraints that can be deferred. A tables, try and find out the scenario in deferred constraint is checked at the end which an operation (insertion, deletion, update) of a transaction and not immediately after on a table or a transaction containing a set each operation of operation on one or more tables violate which constraint on which table IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 38 / 45 Query IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 39 / 45 Query ❱ Mock Data Mock Data App Store Mockaroo create table games ( name VARCHAR(50), version VARCHAR(50), price DECIMAL(3,2) ); insert into games (name, version, price) values ('Sonair', '6.5', 3.68); insert into games (name, version, price) values ('Greenlam', '5.4', 3.91); insert into games (name, version, price) values ('Domainer', '2.9', 8.11); insert into games (name, version, price) values ('Prodder', '6.3', 7.25); ✱ https://www.mockaroo.com/ IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 40 / 45 Query Mock Data App Store ❱ App Store Creating Creating Querying 1. Download the following files✱: AppStoreSchema.sql AppStoreCustomers.sql AppStoreGames.sql AppStoreDownloads.sql 2. Open the file and copy the content to DB Fiddle. The file AppStoreClean.sql is useful to clean up if you run this locally. ✱ Download from Canvas "Files > Cases > AppStore" IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 41 / 45 Query Mock Data App Store ❱ App Store Creating Querying Querying Prints Customers Table SELECT * FROM customers; IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 42 / 45 Query Mock Data App Store ❱ App Store Creating Querying Querying Prints Games Table SELECT * FROM games; IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 43 / 45 Query Mock Data App Store ❱ App Store Creating Querying Querying Prints Downloads Table SELECT * FROM downloads; IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan) 44 / 45 postgres=# exit Press any key to continue... IT5008: Database Design & Programming -- Adi Yoga Sidi Prabawa (notes adapted from Prof. Stephane Bressan)

Document Details

Tags

Related

Full Transcript

Upgrade to continue