Database creation

1. Database creation

• Modul 2

2. Agenda

• Create Data Base
• Tables
• Data types
• Modification of table structure
• Constraints

3. Database design

Database REQUIREMENTS
1.
2.
3.
4.
5.
The company is divided into departments that have names and are
located in different cities.
For each company employee there must be given first and last
name, date of employment, position, monthly rate.
Each employee belongs to only one department.
Each employee has only one boss. The president of the company
does not have a boss.
If the employee works as a salesman, he receives an additional
compensation at the end of year.
6.
Each employee belongs to a group depending on his salary.
7.
For each group, the lower and upper limit of salary is determined.

4. Design DB

5. Demonstration

Demo 1

6. Data types

Basic data types in MS SQL Server are:
• INT – integer numbers;
• NUMERIC(m,n) – fixed point real numbers;
• FLOAT – floating point real numbers;
• CHAR(n) – character string constant of length n characters;
• VARCHAR(n) - character string of variable length,
maximum length n characters;
• DATETIME, DATE, TIME – date and time etc.
NULL – for all types

7. DDL and DML

• DDL - Data Definition Language
• CREATE
• ALTER
• DROP
• DML - Data Manipulation Language
• INSERT
• SELECT
• UPDATE
• DELETE
7

8. Create table

Create a table using the query
CREATE TABLE <table name>
(
<field name 1> <type field 1>,
<field name 2> <type field 2>, …
)
For example,
CREATE TABLE DEPARTMENT (ID INT NOT NULL,
NAME VARCHAR(30),
CITY VARCHAR(30))
8

9. Demonstration

Demo 2
9

10. Modify table structure

For modifying the existing structure of DB tables, we use are
ALTER TABLE and DROP TABLE commands
For modifying the structure of the table, we can do one from
the following:
ADD [COLUMN] – add a new field
ALTER [COLUMN] – modify the field
DROP [COLUMN] – delete the field
ADD CONSTRAINT – add a new constraint
DROP CONSTRAINT – delete the constraint
10

11. Demonstration

Demo 3
11

12. Simple integrity constraint types

Integrity constraint can be divided into the following types:
PRIMARY KEY
UNIQUE
NULL/NOT NULL
FOREIGN KEY/REFERENCE
CHECK
12

13. Demonstration

Demo 4
13

14. Thank you!

US OFFICES
EUROPE OFFICES
Austin, TX
Fort Myers, FL
Lehi, UT
Newport Beach, CA
Waltham, MA
Bulgaria
Germany
Netherlands
Poland
Russia
Sweden
Ukraine
United Kingdom
www.softserveinc.com