Лабораторная работа №1. Проектирование базы данных с использованием ER-технологии

Проектирование базы данных с использованием ER-технологии

Логическая модель

Построение ER-диаграммы

Определим основные сущности: Клиент, Скидка, Услуга, а также Услуга, которая будет соединять неидентифицирующими связями «один-ко-многим».

Графическое представление (IDEF1X)

Физическая модель

 

 

Лабораторная работа №2

Создание и связывание таблиц базы данных

MS SQL

С официального сайта было установлено ПО Microsoft SQL Server 2012. После установки необходимых компонентов в графическом режиме была создана база данных с названием Notarius.

Затем в окне создания запросов выполним скрипт:

USE [master]

GO

CREATE DATABASE [Notarius]

CONTAINMENT = NONE

ON PRIMARY

(NAME = N'Notarius', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Notarius.mdf', SIZE = 5120KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)

LOG ON

(NAME = N'Notarius_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Notarius_log.ldf', SIZE = 1024KB, MAXSIZE = 2048GB, FILEGROWTH = 10%)

GO

ALTER DATABASE [Notarius] SET COMPATIBILITY_LEVEL = 110

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [Notarius].[dbo].[sp_fulltext_database] @action = 'enable'

end

GO

ALTER DATABASE [Notarius] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [Notarius] SET ANSI_NULLS OFF

GO

ALTER DATABASE [Notarius] SET ANSI_PADDING OFF

GO

ALTER DATABASE [Notarius] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [Notarius] SET ARITHABORT OFF

GO

ALTER DATABASE [Notarius] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [Notarius] SET AUTO_CREATE_STATISTICS ON

GO

 

ALTER DATABASE [Notarius] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [Notarius] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [Notarius] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [Notarius] SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE [Notarius] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [Notarius] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [Notarius] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [Notarius] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [Notarius] SET DISABLE_BROKER

GO

ALTER DATABASE [Notarius] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [Notarius] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [Notarius] SET TRUSTWORTHY OFF

GO

ALTER DATABASE [Notarius] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE [Notarius] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [Notarius] SET READ_COMMITTED_SNAPSHOT OFF

GO

ALTER DATABASE [Notarius] SET HONOR_BROKER_PRIORITY OFF

GO

ALTER DATABASE [Notarius] SET RECOVERY SIMPLE

GO

ALTER DATABASE [Notarius] SET MULTI_USER

GO

ALTER DATABASE [Notarius] SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE [Notarius] SET DB_CHAINING OFF

GO

ALTER DATABASE [Notarius] SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF)

GO

ALTER DATABASE [Notarius] SET TARGET_RECOVERY_TIME = 0 SECONDS

GO

ALTER DATABASE [Notarius] SET READ_WRITE

GO

Теперь созданы таблицы с первичными ключами.

ORACLE

С официального сайта было установлено ПО Oracle Database 11g Express Edition и SQLDepeloper (удобное средство для разработчика). После установки необходимых компонентов в графическом режиме было создано новое соединение и пользователь system с определенными правами.

Создать таблицы можно, запустив этот скрипт:

CREATE TABLE "SYSTEM"."KLIENT"

("N_KLIENTA" NUMBER,

"FIO" VARCHAR2(38 BYTE),

"VID_DEYTELNOSTI" VARCHAR2(38 BYTE),

"ADRES" VARCHAR2(38 BYTE),

"TELEFON" VARCHAR2(20 BYTE)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM";

--------------------------------------------------------

-- DDL for Index KLIENT_PK

--------------------------------------------------------

CREATE UNIQUE INDEX "SYSTEM"."KLIENT_PK" ON "SYSTEM"."KLIENT" ("N_KLIENTA")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM";

ALTER TABLE "SYSTEM"."KLIENT" ADD CONSTRAINT "KLIENT_PK" PRIMARY KEY ("N_KLIENTA")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM" ENABLE;

ALTER TABLE "SYSTEM"."KLIENT" MODIFY ("TELEFON" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."KLIENT" MODIFY ("ADRES" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."KLIENT" MODIFY ("VID_DEYTELNOSTI" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."KLIENT" MODIFY ("FIO" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."KLIENT" MODIFY ("N_KLIENTA" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."KLIENT" ADD CONSTRAINT "KLIENT_PK" PRIMARY KEY ("N_KLIENTA")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM" ENABLE;

ALTER TABLE "SYSTEM"."KLIENT" MODIFY ("TELEFON" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."KLIENT" MODIFY ("ADRES" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."KLIENT" MODIFY ("VID_DEYTELNOSTI" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."KLIENT" MODIFY ("FIO" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."KLIENT" MODIFY ("N_KLIENTA" NOT NULL ENABLE);

 

CREATE TABLE "SYSTEM"."SDELKA"

("N_SDELKI" NUMBER,

"N_SKIDKI" NUMBER,

"N_USLUGI" NUMBER,

"N_KLIENTA" NUMBER,

"DATA" DATE,

"SUMMA_SDELKI" NUMBER,

"KOMISSIONIE" NUMBER

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM";

--------------------------------------------------------

-- DDL for Index SDELKA_PK

--------------------------------------------------------

CREATE UNIQUE INDEX "SYSTEM"."SDELKA_PK" ON "SYSTEM"."SDELKA" ("N_SDELKI")

PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM";

--------------------------------------------------------

-- Constraints for Table SDELKA

--------------------------------------------------------

ALTER TABLE "SYSTEM"."SDELKA" ADD CONSTRAINT "SDELKA_PK" PRIMARY KEY ("N_SDELKI")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM" ENABLE;

ALTER TABLE "SYSTEM"."SDELKA" MODIFY ("KOMISSIONIE" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."SDELKA" MODIFY ("SUMMA_SDELKI" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."SDELKA" MODIFY ("DATA" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."SDELKA" MODIFY ("N_KLIENTA" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."SDELKA" MODIFY ("N_USLUGI" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."SDELKA" MODIFY ("N_SKIDKI" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."SDELKA" MODIFY ("N_SDELKI" NOT NULL ENABLE);

--------------------------------------------------------

-- Ref Constraints for Table SDELKA

--------------------------------------------------------

ALTER TABLE "SYSTEM"."SDELKA" ADD CONSTRAINT "N_KLIENTA_FK1" FOREIGN KEY ("N_KLIENTA")

REFERENCES "SYSTEM"."KLIENT" ("N_KLIENTA") ON DELETE CASCADE ENABLE;

ALTER TABLE "SYSTEM"."SDELKA" ADD CONSTRAINT "N_USLUGI_FK1" FOREIGN KEY ("N_USLUGI")

REFERENCES "SYSTEM"."USLUGA" ("N_USLUGI") ENABLE;

ALTER TABLE "SYSTEM"."SDELKA" ADD CONSTRAINT "SKIDKA_FK1" FOREIGN KEY ("N_SKIDKI")

REFERENCES "SYSTEM"."SKIDKA" ("N_SKIDKI") ON DELETE CASCADE DISABLE;

 

CREATE TABLE "SYSTEM"."SKIDKA"

("N_SKIDKI" NUMBER,

"VID_SKIDKI" VARCHAR2(38 BYTE),

"RAZMER_SKIDKI" VARCHAR2(38 BYTE)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM";

--------------------------------------------------------

-- DDL for Index SKIDKA_PK

-------------------------------------------------------

CREATE UNIQUE INDEX "SYSTEM"."SKIDKA_PK" ON "SYSTEM"."SKIDKA" ("N_SKIDKI")

PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM";

--------------------------------------------------------

-- Constraints for Table SKIDKA

--------------------------------------------------------

ALTER TABLE "SYSTEM"."SKIDKA" ADD CONSTRAINT "SKIDKA_PK" PRIMARY KEY ("N_SKIDKI")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM" ENABLE;

ALTER TABLE "SYSTEM"."SKIDKA" MODIFY ("RAZMER_SKIDKI" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."SKIDKA" MODIFY ("VID_SKIDKI" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."SKIDKA" MODIFY ("N_SKIDKI" NOT NULL ENABLE);

 

CREATE TABLE "SYSTEM"."USLUGA"

("N_USLUGI" NUMBER,

"OPISANIE_USLUGI" VARCHAR2(38 BYTE),

"STOIMOST_USLUGI" VARCHAR2(3820 BYTE)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM";

--------------------------------------------------------

-- DDL for Index USLUGA_PK

--------------------------------------------------------

 

CREATE UNIQUE INDEX "SYSTEM"."USLUGA_PK" ON "SYSTEM"."USLUGA" ("N_USLUGI")

PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM";

--------------------------------------------------------

-- Constraints for Table USLUGA

--------------------------------------------------------

ALTER TABLE "SYSTEM"."USLUGA" ADD CONSTRAINT "USLUGA_PK" PRIMARY KEY ("N_USLUGI")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM" ENABLE;

ALTER TABLE "SYSTEM"."USLUGA" MODIFY ("STOIMOST_USLUGI" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."USLUGA" MODIFY ("OPISANIE_USLUGI" NOT NULL ENABLE);

ALTER TABLE "SYSTEM"."USLUGA" MODIFY ("N_USLUGI" NOT NULL ENABLE);

Внешние ключи создаются в Редактирование таблицы: