Лаба2

USE master;
GO

CREATE DATABASE TelephoneNetwork
ON
( NAME = TelephoneNetwork_dat,
FILENAME = 'D:\Учеба\7 семестр\СиМУБД\Лаба2\telephoneNetworkdat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = TelephoneNetwork_log,
FILENAME = 'D:\Учеба\7 семестр\СиМУБД\Лаба2\telephoneNetworklog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB );
GO

USE TelephoneNetwork;
GO

CREATE TABLE Addres (
id int IDENTITY(1,1) PRIMARY KEY,
indexAddress int NOT NULL,
area varchar(30) NOT NULL,
street varchar(30) NOT NULL,
house varchar(30) NOT NULL,
flat varchar(10) NOT NULL
);
GO

CREATE TABLE ATC (
id int IDENTITY(1,1) PRIMARY KEY,
nameATC varchar(30) NOT NULL,
typeATC varchar(30) NOT NULL,
addressId int NOT NULL,
city varchar(30) NOT NULL,
numberOfSubscribers int NOT NULL,
CONSTRAINT FK_ATC_address FOREIGN KEY (addressId)
REFERENCES Addres (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
GO

CREATE TABLE Subscriber (
id int IDENTITY(1,1) PRIMARY KEY,
surname varchar(30) NOT NULL,
nameSubscriber varchar(30) NOT NULL,
patronymic varchar(30) NOT NULL,
typeOfTelephone varchar(30) NOT NULL,
phoneNumber varchar(20) NOT NULL,
intercity varchar(6) NOT NULL,
benefit BIT NOT NULL,
addressId int NOT NULL,
CONSTRAINT FK_subscriber_address FOREIGN KEY (addressId)
REFERENCES Addres (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
GO

CREATE TABLE CallLog (
id int IDENTITY(1,1) PRIMARY KEY,
subscriberId int NOT NULL,
callDate DATE NOT NULL,
startTime TIME NOT NULL,
endTime TIME NOT NULL,
intercity BIT NOT NULL,
addressId int NOT NULL,
CONSTRAINT FK_calllog_subscriber FOREIGN KEY (subscriberId)
REFERENCES Subscriber (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
GO

CREATE TABLE PriceATC (
id int IDENTITY(1,1) PRIMARY KEY,
ATCId int NOT NULL,
cityPrice MONEY NOT NULL,
intercityPrice MONEY NOT NULL,
CONSTRAINT FK_priceATC_ATC FOREIGN KEY (ATCId)
REFERENCES ATC (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
GO

CREATE TABLE SubscriptionFeeSheet (
id int IDENTITY(1,1) PRIMARY KEY,
subscriberId int NOT NULL,
monthSub int NOT NULL,
yearSub int NOT NULL,
cityNumberMinutes int NOT NULL,
intercityNumberMinutes int NOT NULL,
price MONEY NOT NULL,
benefitAmount MONEY NOT NULL,
totalCost MONEY NOT NULL,
CONSTRAINT FK_subFeeSheet_subscriber FOREIGN KEY (subscriberId)
REFERENCES Subscriber (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
GO