advanced web 2012 lecture 3 sean costain 2012. what is a database? sean costain 2012 a database is a...
TRANSCRIPT
![Page 1: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/1.jpg)
Sean Costain 2012
Advanced Web2012
Lecture 3
![Page 2: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/2.jpg)
Sean Costain 2012
What is a Database?A database is a structured way of dealing with structured information.
• An organized mechanism for storing, managing and retrieving information.• Information is stored in tables• Think of a table as a single spreadsheet, a database consists of multiple
spreadsheets that can be linked together using a key.
![Page 3: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/3.jpg)
Sean Costain 2012
Types of Data - Numeric
Tinyint – Use as a boolean
Int – Use for uid/id number
![Page 4: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/4.jpg)
Sean Costain 2012
Types of Data – Numeric cont.
Fixed Point – Decimal, Numeric- Use decimal as numeric defaults to decimal- eg: salary DECIMAL (5,2)Allows for the range -999.99 to 999.99
Floating Point – float, double- Use float- eg: salary FLOAT (7,4)Allows for the range -999.9999 to 999.9999Floating point rounds numbers, so not exact for storage, stick to using decimal.
![Page 5: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/5.jpg)
Sean Costain 2012
Types of Data – Date and Time
DATETIME has a supported range is '1000-01-01 00:00:00' to'9999-12-31 23:59:59'.
TIMESTAMP has a range of'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
The DATETIME and TIMESTAMP type is used for values that contain both date and time parts.
MySQL retrieves and displays DATETIME & TIMESTAMP values in 'YYYY-MM-DD HH:MM:SS' format.
![Page 6: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/6.jpg)
Sean Costain 2012
Types of Data – StringsString Types
- Char- VarChar- Binary- Varbinary- Blob- Text- Enum- Set
The useful String types
- Varchar- Blob- Text
A varchar can have a set length, eg: varchar(4) means that the field being entered can contain exactly 4 characters. Useful for storing simple data like, M/F, names varchar (60)….
![Page 7: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/7.jpg)
Sean Costain 2012
Types of Data – Strings
Blobs- Tinyblob- Blob- Mediumblob- Longblob
BLOB values are treated as binary strings (byte strings).
They have no character set, and sorting and comparison
are based on the numeric values of the bytes in column
values.Blobs can store images
![Page 8: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/8.jpg)
Sean Costain 2012
Types of Data – Strings
Text- Tinytext- Text- Mediumtext- Longtext
TEXT values are treated as nonbinary strings (character
strings). They have a character set, and values are sorted
and compared based on the collation of the character
set.Text stores … text! (strings)
![Page 9: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/9.jpg)
Sean Costain 2012
Types of Data – Size
![Page 10: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/10.jpg)
Sean Costain 2012
Normalisation
Normalisation Rules• 1st Normal Form
• Eliminate repeating groups• Create a table for related data• Identify each table with a primary key
• 2nd Normal Form• Create tables for sets of values that apply to multiple records• Relate these tables with a foreign key
• 3rd Normal Form• Eliminate fields that do not depend on the key
Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.
![Page 11: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/11.jpg)
Sean Costain 2012
Normalisation ExampleSimple spreadsheet that contains student detail.
It works, but sorting by student name or subject would be pretty insane, so we need to normalise the information for storing into a database.
![Page 12: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/12.jpg)
Sean Costain 2012
Normalisation Example Cont.Normalisation on data, break it down and add unique identifiers (uid or id)
So this is the first breakdown of the data, but it’s not broken down enough yet
![Page 13: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/13.jpg)
Sean Costain 2012
Normalisation Example Cont.The address can be broken down further
There has to be links to keep the data consistent
![Page 14: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/14.jpg)
Sean Costain 2012
Normalisation Example Cont.Table linking – Student to address
![Page 15: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/15.jpg)
Sean Costain 2012
Normalisation Example Cont.Table linking – Subject to Date
Though a good break down, reality
doesn’t always fall into 3 rd Normal
form, so you can break the rules.
![Page 16: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/16.jpg)
Sean Costain 2012
Normalisation Example Cont.Table linking – Add Student to Subject and Date
![Page 17: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/17.jpg)
Sean Costain 2012
ERD – Entity Relationship Diagram
Links the tables by Headers
![Page 18: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/18.jpg)
Sean Costain 2012
ERD – Entity Relationship Diagram
All of the tables shown with linking
![Page 19: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/19.jpg)
Sean Costain 2012
phpmyAdmin
Creating a Database- Make a user with the correct privileges- Create a database- Add tables to database- Insert data to tables
Start by having the web server running, get to the web control via- localhost/phpmyadmin
![Page 20: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/20.jpg)
Sean Costain 2012
Creating a userPrivileges- Add User
![Page 21: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/21.jpg)
Sean Costain 2012
Fill in the BlanksWebservers recognise themselves as localhost
![Page 22: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/22.jpg)
Sean Costain 2012
Create Database
Start Adding tables
![Page 23: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/23.jpg)
Sean Costain 2012
Database Fields
Fill in the blanks, such as name, type if it’s auto incrementing and a primary key
![Page 24: Advanced Web 2012 Lecture 3 Sean Costain 2012. What is a Database? Sean Costain 2012 A database is a structured way of dealing with structured information](https://reader035.vdocuments.us/reader035/viewer/2022070404/56649f385503460f94c5526f/html5/thumbnails/24.jpg)
Sean Costain 2012
Database
You should end up with multiple tables for your database