7 trang 6 lượt tải

Lab1CSDL - Hướng dẫn thực hành tạo cơ sở dữ liệu Công ty | Đại học Hoa Sen

12

Create Database Company. Create tables – Primary Key – Foreign Key. Create Database Company. Create tables – Primary Key – Foreign Key. Create Database Company. Create tables – Primary Key – Foreign Key. Create Database Company. Create tables – Primary Key – Foreign Key. Tài liệu giúp bạn tham khảo, ôn tập và đạt kết quả cao. Mời đọc đón xem!

Môn: Hệ quản trị cơ sở dữ liệu (IT 202DV01) 10 tài liệu

Trường: Đại học Hoa Sen 5 K tài liệu

Tác giả:

Profile

Mỹ Châu

3 ngày trước
Danh sách Quiz
/7
lOMoARcPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 1
MSSV:
Họ tên SV:
Notes: Filename: Lab01_HoTen_MSSV.docx
Lab 01
Company Database
1. Create Database Company
CREATE DATABASE Company ON PRIMARY
( NAME = 'Company',
FILENAME = 'E:\DATA\Company.mdf' ,
SIZE = 3072KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = 'Company_log',
FILENAME = 'E:\DATA\Company_log.ldf' ,
SIZE = 1024KB ,
MAXSIZE = 2048KB ,
FILEGROWTH = 10%)
Note: Save to file Company_DB.sql
2. Create tables – Primary Key – Foreign Key
lOMoARcPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 2
Table Structure:
• Department
Field Name Data Type Null
DName varchar(15) NOT NULL
DNumber numeric(4, 0) NOT NULL
Mgrssn char(9) NULL
MgrStartdate datetime NULL
• Employee
Field Name Data Type Null
FName varchar(15) NOT NULL
MInit varchar(1) NULL
LName varchar(15) NOT NULL
SSN char(9) NOT NULL BDate datetime NULL
Address varchar(30) NULL
Sex char(1) NULL
Salary numeric(10, 2) NULL
SuperSSN char(9) NULL
DNo numeric(4, 0) NULL
• Dept_Location
Field Name Data Type Null
DNumber numeric(4, 0) NOT NULL
DLocation varchar(15) NOT NULL
• Project
Field Name Data Type Null
PName varchar(15) NOT NULL
PNumber numeric(4, 0) NOT NULL
PLocation varchar(15) NULL
DNum numeric(4, 0) NOT NULL
• Works_On
Field Name Data Type Null
ESSN char(9) NOT NULL
PNo numeric(4, 0) NOT NULL
Hours numeric(4, 1) NULL
• Dependent
Field Name Data Type Null
ESSN char(9) NOT NULL
Dependent_Nam varchar(15) NOT NULL
e
Sex char(1) NULL
BDate datetime NULL
Relationship varchar(8) NULL
Note: Save to file Company_DB.sql
lOMoARcPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 3
--Tables
[Paste SQL statements here]
--Primary key
[Paste SQL statements here]
--Foreign key
[Paste SQL statements here]
CREATE DATABASE CompanyVDC ON PRIMARY
( NAME = 'CompanyVDC',
FILENAME = 'D:\IT201_DB_nphoang\CompanyVDC.mdf' ,
SIZE = 3072KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = 'CompanyVDC_log',
FILENAME = 'D:\IT201_DB_nphoang\CompanyVDC_log.ldf' ,
SIZE = 1024KB ,
MAXSIZE = 2048KB ,
FILEGROWTH = 10%);
Go use CompanyVDC;
Go
-- tao bang
Create table Department(
Dname varchar(15) not null,
Dnumber numeric(4, 0) not null,
Mgrssn char(9) null,
MgrStartdate datetime null);
Go
Create table employee(
Fname varchar(15) not null,
Minit varchar(1) null,
LName varchar(15) not null,
SSN char(9) not null,
BDate datetime null,
Address varchar(30) null,
sex char(1) null, Salary
numeric(10, 2) null,
SuperSSN char(9) null,
DNo numeric(4, 0) null);
Go
Create table Dept_Location(
DNumber numeric(4, 0) NOT NULL,
DLocation varchar(15) NOT NULL);
Go
Create table Project(
PName varchar(15) NOT NULL,
PNumber numeric(4, 0) NOT NULL,
PLocation varchar(15) NULL,
DNum numeric(4, 0) NOT NULL);
Go
Create table Works_On(
lOMoARcPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 4
ESSN char(9) NOT NULL,
PNo numeric(4, 0) NOT NULL,
Hours numeric(4, 1) NULL);
Go
Create table Dependent(
ESSN char(9) NOT NULL,
Dependent_Name varchar(15) NOT NULL,
Sex char(1) NULL,
BDate datetime NULL,
Relationship varchar(8) NULL);
Go
--Primary key
-- tao khoa chinh
ALTER TABLE Department
ADD Constraint pk_Dept PRIMARY KEY (DNumber);
GO
ALTER TABLE employee
ADD Constraint pk_Emp PRIMARY KEY (SSN);
Go
ALTER TABLE Project
ADD Constraint pk_Prj PRIMARY KEY (PNumber);
Go
ALTER TABLE Dept_Location
ADD Constraint pk_DeptLoc PRIMARY KEY (DNumber, Dlocation);
Go
ALTER TABLE Works_On
ADD Constraint pk_Wks_On PRIMARY KEY (ESSN, PNo);
Go
ALTER TABLE Dependent
ADD Constraint pk_Depd PRIMARY KEY (ESSN,Dependent_Name);
Go
--Foreign key
-- tao khoa ngoai
AlTER TABLE Department
ADD Constraint fk_DeptMgrssn FOREIGN KEY(Mgrssn)
REFERENCES employee(SSN);
Go
ALTER TABLE employee
ADD Constraint fk_EmpDNo FOREIGN KEY(DNo)
REFERENCES Department(Dnumber);
Go
ALTER TABLE employee
ADD Constraint fk_EmpSuperSSN FOREIGN KEY(SuperSSN)
REFERENCES employee(SSN);
Go
ALTER TABLE Dept_Location
ADD Constraint fk_Dpt_lt FOREIGN KEY(DNumber)
REFERENCES Department(Dnumber);
Go
AlTER TABLE Project
ADD Constraint fk_ProjDNum FOREIGN KEY(DNum)
REFERENCES DEPARTMENT(Dnumber);
Go
ALTER TABLE Works_On
ADD Constraint fk_Wks_On_Essn FOREIGN KEY(ESSN)
REFERENCES employee(SSN);
Go
ALTER TABLE Works_On
ADD Constraint fk_Wks_On_PNo FOREIGN KEY(PNo)
lOMoARcPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 5
REFERENCES Project(PNumber);
Go
ALTER TABLE Dependent
ADD Constraint fk_Depd_ESSN FOREIGN KEY(ESSN)
REFERENCES employee(SSN);
Go
Insert into Department values ('Headquarters', 1, NULL,'06/19/1971')
Go
Insert into Department values ('Administration', 4, NULL, '01/01/1985')
Go
Insert into Department values ('Research', 5, NULL, '05/22/1978')
Go
Insert into Employee values ('James', 'E', 'Borg', '888665555', '11/10/1927',
'Houston, TX', 'M', 55000, NULL, '1')
Go
Insert into Employee values ('Franklin', 'T', 'Wong', '333445555', '12/08/1945',
'Houston, TX', 'M', 40000, '888665555', '5')
Go
Insert into Employee values ('John', 'B', 'Smith', '123456789', '01/09/1955',
'Houston, TX', 'M', 30000, '333445555', '5')
Go
Insert into Employee values ('Joyce', 'A', 'English', '453453453',
'07/31/1962', 'Houston, TX', 'F', 25000, '333445555', '5')
Go
Insert into Employee values ('Ramesh', 'K', 'Narayan', '666884444',
'09/15/1952', 'Humble, TX', 'M', 38000, '333445555', '5') Go
Insert into Employee values ('Jennifer', 'S', 'Wallace', '987654321',
'06/20/1931', 'Bellaire, TX', 'F', 43000, '888665555', '4')
Go
Insert into Employee values ('Ahmad', 'V', 'Jabbar', '987987987',
'03/29/1959', 'Houston, TX', 'M', 25000, '987654321', '4')
Go
Insert into Employee values ('Alicia', 'J', 'Zelaya', '999887777',
'07/19/1958', 'Spring, TX', 'F', 25000, '987654321', '4')
Go
-- Cap nhat MgrSSN trong Department
update Department
Set MgrSsn = '888665555'
Where Dnumber = 1;
Go
update Department
Set MgrSsn = '987654321'
Where Dnumber = 4;
Go
update Department
Set MgrSsn = '333445555'
Where Dnumber = 5; Go
select * from Department;
-- Dept_Location
Insert into
Dept_Location values
(1, Go
'Houston')
lOMoARcPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 6
Insert into
Dept_Location values
(4, Go
'Stafford')
Insert into
Dept_Location values
(5, Go
'Bellaire')
Insert into
Dept_Location values
(5, Go
'Houston')
Insert into
Dept_Location values
(5, Go
'Sugarland')
Insert into Project values
('ProductX', 1, Go
'Bellaire', 5)
Insert into Project values
('ProductY', 2, Go
'Sugarland', 5)
Insert into Project values
('ProductZ', 3, Go
'Houston', 5)
Insert into Project values
('ProductX', 10, Go
'Stafford', 4)
Insert into Project values
('Computerization', Go
20,
'Houston',
1)
Insert into Project values
('Newbenefits', Go
-- Works_On
30,
'Stafford',
4)
Insert into Works_On values
('123456789',1, Go
32.5)
Insert into Works_On values
('123456789',2, Go
7.5)
Insert into Works_On values
('333445555',2, Go
10.0)
Insert into Works_On values
('333445555',3, Go
10.0)
Insert into Works_On values
('333445555',10, Go
10.0)
Insert into Works_On values
('333445555',20, Go
10.0)
Insert into Works_On values
('453453453',1, Go
20.0)
Insert into Works_On values
('453453453',2, Go
20.0)
Insert into Works_On values
('666884444',3, Go
40.0)
Insert into Works_On values
('888665555',20, Go
NULL)
Insert into Works_On values
('987654321',20, Go
15.0)
Insert into Works_On values
('987654321',30, Go
20.0)
Insert into Works_On values
('987987987',10, Go
35.0)
Insert into Works_On values
('987987987',30, Go
5.0)
Insert into Works_On values
('999887777',10, Go
10.0)
Insert into Works_On values
('999887777',30, Go
30.0)
lOMoARcPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 7
-- Dependent
Insert into Dependent values ('123456789', 'Alice', 'F', '12/31/1978','Daughter')
Go
Insert into Dependent values ('123456789', 'Elizabeth', 'F', '05/05/1957','Spouse')
Go
Insert into Dependent values ('123456789', 'Michael', 'M', '01/01/1978','Son')
Go
Insert into Dependent values ('333445555', 'Alice', 'F', '04/05/1976','Daughter')
Go
Insert into Dependent values ('333445555', 'Joy', 'F', '05/03/1948','Spouse')
Go
Insert into Dependent values ('333445555', 'Theodore', 'M', '10/25/1973','Son')
Go
Insert into Dependent values ('987654321', 'Abner', 'M', '02/29/1932','Spouse')
Go
END

Tài liệu khác của Đại học Hoa Sen

Preview text:

lOMoAR cPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 1 MSSV: Họ tên SV:
Notes: Filename: Lab01_HoTen_MSSV.docx Lab 01 Company Database
1. Create Database Company
CREATE DATABASE Company ON PRIMARY ( NAME = 'Company',
FILENAME = 'E:\DATA\Company.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = 'Company_log',
FILENAME = 'E:\DATA\Company_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048KB , FILEGROWTH = 10%)
Note: Save to file Company_DB.sql
2. Create tables – Primary Key – Foreign Key lOMoAR cPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 2 Table Structure: • Department Field Name Data Type Null DName varchar(15) NOT NULL DNumber numeric(4, 0) NOT NULL Mgrssn char(9) NULL MgrStartdate datetime NULL • Employee Field Name Data Type Null FName varchar(15) NOT NULL MInit varchar(1) NULL LName varchar(15) NOT NULL
SSN char(9) NOT NULL BDate datetime NULL Address varchar(30) NULL Sex char(1) NULL Salary numeric(10, 2) NULL SuperSSN char(9) NULL DNo numeric(4, 0) NULL • Dept_Location Field Name Data Type Null DNumber numeric(4, 0) NOT NULL DLocation varchar(15) NOT NULL • Project Field Name Data Type Null PName varchar(15) NOT NULL PNumber numeric(4, 0) NOT NULL PLocation varchar(15) NULL DNum numeric(4, 0) NOT NULL • Works_On Field Name Data Type Null ESSN char(9) NOT NULL PNo numeric(4, 0) NOT NULL Hours numeric(4, 1) NULL • Dependent Field Name Data Type Null ESSN char(9) NOT NULL Dependent_Nam varchar(15) NOT NULL e Sex char(1) NULL BDate datetime NULL Relationship varchar(8) NULL
Note: Save to file Company_DB.sql lOMoAR cPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 3 --Tables [Paste SQL statements here] --Primary key [Paste SQL statements here] --Foreign key [Paste SQL statements here]
CREATE DATABASE CompanyVDC ON PRIMARY ( NAME = 'CompanyVDC',
FILENAME = 'D:\IT201_DB_nphoang\CompanyVDC.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = 'CompanyVDC_log',
FILENAME = 'D:\IT201_DB_nphoang\CompanyVDC_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048KB , FILEGROWTH = 10%); Go use CompanyVDC; Go -- tao bang Create table Department( Dname varchar(15) not null,
Dnumber numeric(4, 0) not null, Mgrssn char(9) null, MgrStartdate datetime null); Go Create table employee( Fname varchar(15) not null, Minit varchar(1) null, LName varchar(15) not null, SSN char(9) not null, BDate datetime null, Address varchar(30) null, sex char(1) null, Salary numeric(10, 2) null, SuperSSN char(9) null, DNo numeric(4, 0) null); Go Create table Dept_Location(
DNumber numeric(4, 0) NOT NULL,
DLocation varchar(15) NOT NULL); Go Create table Project( PName varchar(15) NOT NULL,
PNumber numeric(4, 0) NOT NULL, PLocation varchar(15) NULL,
DNum numeric(4, 0) NOT NULL); Go Create table Works_On( lOMoAR cPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 4 ESSN char(9) NOT NULL, PNo numeric(4, 0) NOT NULL, Hours numeric(4, 1) NULL); Go Create table Dependent( ESSN char(9) NOT NULL,
Dependent_Name varchar(15) NOT NULL, Sex char(1) NULL, BDate datetime NULL,
Relationship varchar(8) NULL); Go --Primary key -- tao khoa chinh ALTER TABLE Department
ADD Constraint pk_Dept PRIMARY KEY (DNumber); GO ALTER TABLE employee
ADD Constraint pk_Emp PRIMARY KEY (SSN); Go ALTER TABLE Project
ADD Constraint pk_Prj PRIMARY KEY (PNumber); Go ALTER TABLE Dept_Location
ADD Constraint pk_DeptLoc PRIMARY KEY (DNumber, Dlocation); Go ALTER TABLE Works_On
ADD Constraint pk_Wks_On PRIMARY KEY (ESSN, PNo); Go ALTER TABLE Dependent
ADD Constraint pk_Depd PRIMARY KEY (ESSN,Dependent_Name); Go --Foreign key -- tao khoa ngoai AlTER TABLE Department
ADD Constraint fk_DeptMgrssn FOREIGN KEY(Mgrssn) REFERENCES employee(SSN); Go ALTER TABLE employee
ADD Constraint fk_EmpDNo FOREIGN KEY(DNo)
REFERENCES Department(Dnumber); Go ALTER TABLE employee
ADD Constraint fk_EmpSuperSSN FOREIGN KEY(SuperSSN) REFERENCES employee(SSN); Go ALTER TABLE Dept_Location
ADD Constraint fk_Dpt_lt FOREIGN KEY(DNumber)
REFERENCES Department(Dnumber); Go AlTER TABLE Project
ADD Constraint fk_ProjDNum FOREIGN KEY(DNum)
REFERENCES DEPARTMENT(Dnumber); Go ALTER TABLE Works_On
ADD Constraint fk_Wks_On_Essn FOREIGN KEY(ESSN) REFERENCES employee(SSN); Go ALTER TABLE Works_On
ADD Constraint fk_Wks_On_PNo FOREIGN KEY(PNo) lOMoAR cPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 5 REFERENCES Project(PNumber); Go ALTER TABLE Dependent
ADD Constraint fk_Depd_ESSN FOREIGN KEY(ESSN) REFERENCES employee(SSN); Go
Insert into Department values ('Headquarters', 1, NULL,'06/19/1971') Go
Insert into Department values ('Administration', 4, NULL, '01/01/1985') Go
Insert into Department values ('Research', 5, NULL, '05/22/1978') Go
Insert into Employee values ('James', 'E', 'Borg', '888665555', '11/10/1927',
'Houston, TX', 'M', 55000, NULL, '1') Go
Insert into Employee values ('Franklin', 'T', 'Wong', '333445555', '12/08/1945',
'Houston, TX', 'M', 40000, '888665555', '5') Go
Insert into Employee values ('John', 'B', 'Smith', '123456789', '01/09/1955',
'Houston, TX', 'M', 30000, '333445555', '5') Go
Insert into Employee values ('Joyce', 'A', 'English', '453453453',
'07/31/1962', 'Houston, TX', 'F', 25000, '333445555', '5') Go
Insert into Employee values ('Ramesh', 'K', 'Narayan', '666884444',
'09/15/1952', 'Humble, TX', 'M', 38000, '333445555', '5') Go
Insert into Employee values ('Jennifer', 'S', 'Wallace', '987654321', '06/20/1931', 'Bellaire, TX', 'F', 43000, '888665555', '4') Go
Insert into Employee values ('Ahmad', 'V', 'Jabbar', '987987987', '03/29/1959', 'Houston, TX', 'M', 25000, '987654321', '4') Go
Insert into Employee values ('Alicia', 'J', 'Zelaya', '999887777', '07/19/1958',
'Spring, TX', 'F', 25000, '987654321', '4') Go
-- Cap nhat MgrSSN trong Department update Department Set MgrSsn = '888665555' Where Dnumber = 1; Go update Department Set MgrSsn = '987654321' Where Dnumber = 4; Go update Department Set MgrSsn = '333445555' Where Dnumber = 5; Go select * from Department; -- Dept_Location Insert into 'Houston') Dept_Location values (1, Go lOMoAR cPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 6 Insert into 'Stafford') Dept_Location values (4, Go Insert into 'Bellaire') Dept_Location values (5, Go Insert into 'Houston') Dept_Location values (5, Go Insert into 'Sugarland') Dept_Location values (5, Go Insert into Project values 'Bellaire', 5) ('ProductX', 1, Go Insert into Project values 'Sugarland', 5) ('ProductY', 2, Go Insert into Project values 'Houston', 5) ('ProductZ', 3, Go Insert into Project values 'Stafford', 4) ('ProductX', 10, Go Insert into Project values 20, 'Houston', 1) ('Computerization', Go Insert into Project values 30, 'Stafford', 4) ('Newbenefits', Go -- Works_On Insert into Works_On values 32.5) ('123456789',1, Go Insert into Works_On values 7.5) ('123456789',2, Go Insert into Works_On values 10.0) ('333445555',2, Go Insert into Works_On values 10.0) ('333445555',3, Go Insert into Works_On values 10.0) ('333445555',10, Go Insert into Works_On values 10.0) ('333445555',20, Go Insert into Works_On values 20.0) ('453453453',1, Go Insert into Works_On values 20.0) ('453453453',2, Go Insert into Works_On values 40.0) ('666884444',3, Go Insert into Works_On values NULL) ('888665555',20, Go Insert into Works_On values 15.0) ('987654321',20, Go Insert into Works_On values 20.0) ('987654321',30, Go Insert into Works_On values 35.0) ('987987987',10, Go Insert into Works_On values 5.0) ('987987987',30, Go Insert into Works_On values 10.0) ('999887777',10, Go Insert into Works_On values 30.0) ('999887777',30, Go lOMoAR cPSD| 47206071
106ded28af7a3175880d9d0e48b4a528.docx 7 -- Dependent
Insert into Dependent values ('123456789', 'Alice', 'F', '12/31/1978','Daughter') Go
Insert into Dependent values ('123456789', 'Elizabeth', 'F', '05/05/1957','Spouse') Go
Insert into Dependent values ('123456789', 'Michael', 'M', '01/01/1978','Son') Go
Insert into Dependent values ('333445555', 'Alice', 'F', '04/05/1976','Daughter') Go
Insert into Dependent values ('333445555', 'Joy', 'F', '05/03/1948','Spouse') Go
Insert into Dependent values ('333445555', 'Theodore', 'M', '10/25/1973','Son') Go
Insert into Dependent values ('987654321', 'Abner', 'M', '02/29/1932','Spouse') Go END

Tài liệu liên quan: