data base management system · dbms (database management system): - collection programs that...
TRANSCRIPT
Data Base Management System LAB LECTURES
Taif University
faculty of Computers and Information Technology
First Semester 34-1435 H
A. Arwa Bokhari & A. Khlood Alharthi & A. Aamal Alghamdi
In This Lecture: - Database & Database Management System. - DBMS Functions - Some Terms - Structured Query Language - Data Types in HeidiSQL - Installing HeidiSQL and Wamp server - Connecting to the DBMS - Create a new Database - Create a table
Database & DBMS
Database
- Collection of related data that houses: * End user data ( raw facts) * Metadata ( data about data)
> raw facts.-Student name Example: Datatype of student name -> metadata
DBMS (Database Management System):
- Collection programs that manages database structure and controls access to data. - Possible to share data among multiple applications or users. - Makes data Management more efficient and effective.
DBMS Functions
Database Management System Functions:
- Performs functions that guarantee integrity and consistency
of data. - Data Storage management - Data transformation and presentation. - Security management. - Multi-user access control. - Backup and recovery management - Data integrity management. - Database access languages and application programming interfaces. - Database communication interfaces.
Some Terms
Entity:
Anything about witch data are to be collected and stored.
Entity set:
Is collection of like entities.
Attribute:
Is a characteristic of an entity.
Relationship:
Describes an association among (two or more) entities (one to one, one to many, many to many).
Table:
Matrix consisting of a series of row/column intersections.
Structured Query Language
SQL:
Structured Query Language is the standard language used to communicate with database software.
Data types in HeidiSql
.
Type {storage}
Name Range Attributes Default
Numeric {1 byte}
TINYINT[(M)] -128 TO 127
[0 to 255 if UNSIGNED]
AUTO_INCREMENT UNSIGNED, ZEROFILL,
SERIAL DEFAULT VALUE
NULL [0 if NOT NULL]
Numeric {2 bytes}
SMALLINT[(M)] -32,768 to 32,767
[0 to 65,535]
AUTO_INCREMENT, UNSIGNED, ZEROFILL,
SERIAL DEFAULT VALUE
NULL [0 if NOT NULL]
Numeric {3 bytes}
MEDIUMINT[(M)] -8,388,608 to 8,388,607
[0 to 16,777,215]
AUTO_INCREMENT, UNSIGNED, ZEROFILL,
SERIAL DEFAULT VALUE
NULL [0 if NOT NULL]
Numeric {4 bytes}
INT[(M)] -/+2.147E+9
[0 to 4.294E+9]
AUTO_INCREMENT, UNSIGNED, ZEROFILL,
SERIAL DEFAULT VALUE
NULL [0 if NOT NULL]
Numeric {4 or 8}
FLOAT(p) p=0-24 --> "FLOAT"
p=25-53 --> "DOUBLE" UNSIGNED, ZEROFILL
NULL [0 if NOT NULL]
Data types in HeidiSql
.
Type {storage}
Name Range Attributes Default
String {M char's}
CHAR[(M)]
M=0-255 Characters, FIXED.
Right padded with spaces.
BINARY, CHARACTER SET
NULL ["" if NOT NULL]
String {M char's1}
VARCHAR(M) M=0-65,535 Characters
M=0-255 <v5.0.3 BINARY,
CHARACTER SET NULL
["" if NOT NULL]
Date & Time {3 bytes}
DATE "1000-01-01" - "9999-12-
31" Global Only
(YYYY-MM-DD)
NULL ["0000-00-00" if
NOT NULL]
Date & Time {8 bytes}
DATETIME "1000-01-01 00:00:00" - "9999-12-31 23:59:59"
Global Only (YYYY-MM-DD
hh:mm:ss)
NULL ["0000-00-00 00:00:00" if NOT NULL]
Date & Time {3 bytes}
DATE "1000-01-01" - "9999-12-
31" Global Only
(YYYY-MM-DD)
NULL ["0000-00-00" if
NOT NULL]
Installing HeidiSQL and Wamp server
- Go to one of these sites: https://sites.google.com/site/arwashakr/
sites.google.com/site/aamalfal/://https - Download wamp and Heidisql files . - Download wamp server and Heidisql videos. - Install wamp server and Heidisql as shown in that videos.
Connecting to the DBMS
by clicking on the server OnlinePut the server -1icon (left click) and choose “ Put Online”.
1 2
2- Open Heidisql program from the start menu .
3- Choose “ Connect!” as the following dialog .
3
Create a new database
1- From Tools menu , choose create database .
click on this icon : or
2- Write DB name and change the default character set and collation as the next figure :
Create a table
1- From Tools menu , choose create new table .
click on this icon : or
2- Write table information, choose table type and change the default character set and collation as the next figure (do the same for all tables)
1- Course table : 2- Section table :
3- Grade_report table : 4- prerequisite table :
1- Course table. 2- Grade_report table. 3- prerequisite table . 4- Section table.