Bai giai CSDL StudentManagement

create database StudentManagement on(
name='data',
filename='D:\ltmloan\data.mdf'
)
log on (
name='data_log',
filename='D:\ltmloan\data_log.mdf'
)
--------------------------------------------------------
create table UserTypes(
UserTypeCode int not null,
UDesc varchar(20)
)

create table Users (
UserID int not null,
UserTypeCode int,
Username varchar(20),
Pass_word varchar(20)
)
--Them cot
alter table Users add column_a varchar(12)

--Sua doi kieu du lieu
alter table Users alter column column_a int
alter table Users alter column column_a int not null

--Sua doi thanh rang buoc Unique,primary key, identity
ALTER TABLE Users ADD CONSTRAINT con_primary primary key (UserID)
ALTER TABLE Users ADD CONSTRAINT con_unique UNIQUE (column_a)

--Sua doi thanh rang buoc Check, default
alter table Users add CONSTRAINT check_user check(column_a >=10)

--Sua doi rang buoc khoa ngoai
ALTER TABLE Users ADD CONSTRAINT column_c_fk
foreign key (UserTypeCode) REFERENCES UserTypes(UserTypeCode)
--Xoa cot
alter table UserTypes drop column abc
--------------------------------------------------------


create table Course (
CourseCode int primary key,
CDesc varchar (20) null,
totalFeeCash money check (totalfeecash>0),
totalFeeInstallment money null,
MinDownPayment money null,
CourseType varchar(10) null,
Duration int null
)

create table material(
MaterialCode int primary key identity (1,1),
MaterialType varchar(10),
MaterialName varchar(10),
MDesc varchar(10) default 'None'
)

create table CourseMaterial(
CourseCode int Not null,
MaterialCode int not null,
YearSem varchar(15),
Sequence int,
primary key (CourseCode,MaterialCode),
foreign key (CourseCode) references Course (CourseCode),
foreign key (MaterialCode) references material(MaterialCode)
)

CREATE TABLE Batch(
BatchNo INT PRIMARY KEY identity(1,1),
DateStarted DATETIME NOT NULL check (DateStarted>='20/12/2006'),
NoofStudentsEnrolled INT NOT NULL,
MinimumNumberOfStudents INT NOT NULL check (MinimumNumberOfStudents<=24),
MaximumNumberOfStudents INT NOT NULL default 24,
CourseCode INT NOT NULL,
BatchTiming VARCHAR(15) NOT NULL,
BatchOver BIT NOT NULL,
FOREIGN KEY (CourseCode) REFERENCES Course(CourseCode)
)

CREATE TABLE Students(
RollNo INT NOT NULL PRIMARY KEY,
StudentName VARCHAR(20) NOT NULL,
EnrollmentDate DATETIME NOT NULL,
CourseCode INT NOT NULL,
Qualification VARCHAR(20) NOT NULL,
Address VARCHAR(100) NOT NULL,
Email VARCHAR(20) NOT NULL,
ContactNo BIGINT NOT NULL,
Opinion VARCHAR(100) NOT NULL,
Status VARCHAR(20) NOT NULL,
FeeOpted VARCHAR(15) NOT NULL check (FeeOpted in('FULL CASH','INSTALLMENT')),
NumberOfInstallments TINYINT NOT NULL,
BatchNo INT NOT NULL,
FOREIGN KEY (CourseCode) REFERENCES Course(CourseCode),
FOREIGN KEY (BatchNo) REFERENCES Batch(BatchNo)
)