Выполнить запросы в MySQL

Проектирование информационных систем

 

 

Выполнил студент группы _____

_09.03.03 Прикладная информатика ___

направление подготовки

 

_________________________________________________

(Ф.И.О.)

Руководитель работы:

Филимоненкова Т.Н.

 

Дата подачи на кафедру

«_____» ________________ 20__ года

Ответственный за регистрацию: ст. лаборант Савченко О.Ю.

Работа выполнена на оценку:

По нац.шкале	Количество баллов	ECTS	Подпись преподавателя

 

Преподаватель_________________________ _____________________

 

Дата _____________________

 

Ялта 20__

Вариант __

Теоретический вопрос:

Модель «сущность-связь»: сущность, атрибуты, ключи. Обеспечение целостности данных.

Модель “сущность-связь” (Entity-Relationship model или ER – модель) представляет собой высокоуровневую концептуальную модель данных, которая была разработана с целью упрощения задачи проектирования структур баз данных.

Данная модель представляет собой набор концепций, которые описывают структуру БД в виде совокупности сущностей, атрибутов и связей.

СУЩНОСТЬ– это множество объектов реального мира с одинаковыми свойствами. Сущность характеризуется независимым существованием и может быть объектом с фи­зическим (или реальным) существова­нием или объектом с концептуальным (или абстрактным) существо­ванием.

Сущность представляет собой основное содержание того явления или процесса (транзакции или запроса), о котором необходимо собрать информацию, и является узловой точкой сбора информации. Сущность относится к набору однородных предметов или вещей. Каждая сущность идентифицируется именем и списком свойств (атрибутов). В качестве сущности может выступать личность, место, вещь и т.д., ин­формацию о которых необходимо хранить в БД.

АТРИБУТ — это средство, с помощью которого определяются свойства сущности или связи. Атрибут — это поименованная характеристика сущности. Наименование атрибута должно быть уникальным для конкретной сущности, но может быть одинаковым для разных сущностей.

Конкретный набор атрибутов для сущности определяется задачами, в которых они используются.

СВЯЗЬ – это отношение между экземплярами двух (и более) разных сущностей. Механизм связей используется для того, чтобы определить взаимоотношения между сущностями. Кроме этого, существуют отношения между атрибутами отдельной сущности.

Каждой связи присваивается имя, которое должно описывать его функцию. Связи обладают такими характеристиками, как наименование связи, показатель кардинальности, степень участия, степень связи, время существования связи и другими.

Целостность базы данных – соответствие имеющейся в базе данных информации её внутренней логике, структуре и всем явно заданным правилам. Каждое правило, налагающее некоторое ограничение на возможное состояние базы данных, называется ограничением целостности.

Целостность базы данных поддерживается с помощью набора специальных логических правил, накладываемых на данные, называемых ограничениями целостности. Ограничения целостности представляют собой утверждения о допустимых значениях отдельных информационных единиц и связях между ними. Целостность данных подразделяется на следующие категории:

1. Сущностная целостность – определяет строку как уникальную сущность в конкретной таблице. Она обеспечивает целостность столбцов идентификаторов или первичного ключа таблицы с помощью индексов и ограничений UNIQUE или PRIMARY KEY.

2. Доменная целостность – это достоверность записей в конкретном столбце. Она включает ограничения типа данных, ограничения формата при помощи ограничений CHECK и правил, а также ограничения диапазона возможных значений при помощи ограничений FOREIGN KEY, CHECK, DEFAULT, определений NOT NULL и правил.

3. Ссылочная целостность – сохраняет определенные связи между таблицами при добавлении или удалении строк

Постановка задачи

Автосервис

Компания оказывает услуги по ремонту и сервисному обслуживанию автомобилей. На автосервисе работают несколько специалистов и автомехаников, которые могут выполнять ремонтные работы. Зарплата персонала зависит от количества и стоимости выполненных работ. На фирме имеется прейскурант ремонтных услуг и их стоимости. О клиентах хранится информация: ФИО, адрес, телефон. При приеме автомобиля на ремонт или обслуживание заносятся следующие данные: госномер авто, марка, год выпуска, кому принадлежит. Учесть, что клиент может иметь несколько автомобилей. При приеме автомобиля в ремонт составляется договор, в котором указывается, какой автомобиль принят на ремонт, дата приема, какой сотрудник выполняет ремонт, дата выполнения заказа. Учесть, что автомобиль может требовать несколько видов ремонтных работ.

 

Выполнить запросы в MySQL

1. Вывести список автомобилей со всеми их данными и с ФИО их владельцев.

2. Вывести сведения о договорах в формате: дата договора, госномер и марка машины.

3. Посчитать общую стоимость всех оказанных услуг по договорам

ER-диаграмма базы данных autoservice

 

 

SQL-инструкции создания базы данных в клиент-серверной СУБД MySQL

CREATE TABLE Avto (

id_avto INTEGER NOT NULL,

gos_nomer VARCHAR(10) NULL,

god_vipuska INTEGER NULL,

color VARCHAR(20) NULL,

marka VARCHAR(20) NULL,

id_client INTEGER NULL

);

CREATE UNIQUE INDEX XPKAvto ON Avto (

id_avto

);

ALTER TABLE Avto

ADD PRIMARY KEY (id_avto);

CREATE INDEX XIF1Avto ON Avto (

id_client

);

CREATE TABLE Client (

id_client INTEGER NOT NULL,

fio VARCHAR(45) NULL,

telephone VARCHAR(20) NULL

);

CREATE UNIQUE INDEX XPKClient ON Client (

id_client

);

ALTER TABLE Client ADD PRIMARY KEY (id_client);

CREATE TABLE Sotrudniki (

id_sotr INTEGER NOT NULL,

fio VARCHAR(45) NULL,

id_spec INTEGER NULL,

oklad NUMERIC(7,2) NULL

);

CREATE UNIQUE INDEX XPKSotrudniki ON Sotrudniki (

id_sotr);

ALTER TABLE Sotrudniki ADD PRIMARY KEY (id_sotr);

CREATE INDEX XIF1Sotrudniki ON Sotrudniki(

id_spec);

CREATE TABLE Specializacia (

id_spec INTEGER NOT NULL,

name_spec VARCHAR(30) NULL

);

CREATE UNIQUE INDEX XPKSpecialnost ON Specializacia (

id_spec);

ALTER TABLE Specializacia ADD PRIMARY KEY (id_spec);

CREATE TABLE Uslugi (

id_rabot INTEGER NOT NULL,

title VARCHAR(45) NULL,

price NUMERIC(7,2) NULL

);

CREATE UNIQUE INDEX XPKUslugi ON Uslugi (

id_rabot);

ALTER TABLE Uslugi ADD PRIMARY KEY (id_rabot);

CREATE TABLE Zakaz (

id_zakaza INTEGER NOT NULL,

data_prin DATE NULL,

stoimost NUMERIC(7,2) NULL,

data_vidachi DATE NULL,

id_avto INTEGER NULL,

id_sotr INTEGER NULL

);

CREATE UNIQUE INDEX XPKZakaz ON Zakaz (

id_zakaza);

ALTER TABLE Zakaz ADD PRIMARY KEY (id_zakaza);

CREATE INDEX XIF1Zakaz ON Zakaz (

id_avto);

CREATE INDEX XIF2Zakaz ON Zakaz (

id_sotr);

CREATE TABLE zakaz_item (

id_zakaza INTEGER NOT NULL,

id_rabot INTEGER NOT NULL

);

CREATE UNIQUE INDEX XPKzakaz_item ON zakaz_item (

id_zakaza,

id_rabot

);

ALTER TABLE zakaz_item

ADD PRIMARY KEY (id_zakaza,id_rabot);

CREATE INDEX XIF1zakaz_item ON zakaz_item (

id_zakaza);

CREATE INDEX XIF2zakaz_item ON zakaz_item (

id_rabot);

ALTER TABLE Avto

ADD FOREIGN KEY R_16 (id_client) REFERENCES Client(id_client)

ON DELETE CASCADE;

ALTER TABLE Sotrudniki

ADD FOREIGN KEY R_1 (id_spec) REFERENCES Specializacia(id_spec)

ON DELETE CASCADE;

ALTER TABLE Zakaz

ADD FOREIGN KEY R_3 (id_avto) REFERENCES Avto(id_avto)

ON DELETE CASCADE;

ALTER TABLE Zakaz

ADD FOREIGN KEY R_12 (id_sotr) REFERENCES Sotrudniki(id_sotr)

ON DELETE CASCADE;

ALTER TABLE zakaz_item

ADD FOREIGN KEY R_10 (id_zakaza) REFERENCES Zakaz(id_zakaza)

ON DELETE CASCADE

;

ALTER TABLE zakaz_item

ADD FOREIGN KEY R_11 (id_rabot) REFERENCES Uslugi(id_rabot)

ON DELETE CASCADE;

Данные в таблицах

 

 

 

 

 

 

Запросы к базе данных.

 

1. Вывести список автомобилей со всеми их данными и с ФИО их владельцев.

select id_avto,gos_nomer,marka,fio from avto,client where avto.id_client=client.id_client;

 

 

 

2. Вывести сведения о договорах в формате: дата договора, госномер и марка машины.

select data_prin,zakaz.id_avto,gos_nomer,marka from zakaz,avto where zakaz.id_avto=avto.id_avto;

 

3. Посчитать общую стоимость всех оказанных услуг по договорам

select sum(stoimost) as common_sum from zakaz;