Практикум по Базам Данных (Методические материалы) (1127013), страница 3

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 3)

Очевидное решениевыглядит следующим образом:SELECT AVG(quantity) AS average_quantity FROM itemaverage_quantity---------------------------------------81Проблема заключается в том, что результат применения агрегатной функции в SQL Server имееттот же тип, что и её аргумент. Таким образом, в этом примере необходимо преобразоватьзначения целочисленного столбца quantity:SELECT AVG(1.0*quantity) AS average_quantity FROM itemaverage_quantity--------------------------------------81.881918То же самое необходимо помнить и при делении.Примеры вопросов по обязательной частиОбъяснить, как работают написанные запросы.Рассказать про операцию соединения (JOIN) и различные её разновидности.Рассказать про агрегатные функции, предложения GROUP BY и HAVING.Как выбрать только уникальные значения какого-либо столбца?Как осуществить сортировку по возрастанию/убыванию по значению какого-либо столбца?Как агрегатные функции ведут себя по отношению к неопределённым значениям?Рассказать о теоретико-множественных операциях в SQL.Чем отличаются UNION и UNION ALL?Чем отличаются COUNT(*) и COUNT(field)?Как подсчитать количество уникальных значений столбца?Как можно осуществить проверку на неопределенное значение?Рассказать про предикат LIKE.Как можно выбрать только определенное количество строк?Чем SQL-таблица отличается от отношения?Примеры дополнительных вопросовИсправить неверно работающий запрос (запросы).Упростить один или несколько запросов.Округлить результирующее значение до 3 знаков после точки.Округлить вещественное число до целого без нулей после точки.Переписать запрос, не используя функцию MAX (MIN).Изменить формат вывода данных (например, формат даты и времени).9 Написать или модифицировать запрос по сформулированному заданию.10Практическое задание №2.

Проектирование схемы базы данныхПостановка задачиВторое практическое задание связано с проектированием схемы базы данных. Каждыйиндивидуальный вариант содержит ER-диаграмму некоторой предметной области, иногда оченьприблизительную (она может быть модифицирована, но не в сторону упрощения). Задачейстудента является решить, для чего будет использоваться создаваемая база данных, и, исходя изэтого, построить её концептуальную схему. Результатом данного практического задания являетсясхема базы данных (в виде диаграммы, содержащей таблицы и связи между ними, без уточнениятипов столбцов). Для проектирования схемы и построения диаграммы рекомендуетсяиспользовать среду Microsoft SQL Server Management Studio, хотя можно использовать и другиесредства.

Максимальная оценка за данное задание всегда составляет 8 баллов, а студент можетсдавать схему несколько раз, исправляя замечания преподавателей. При сдаче задания студентдолжен обосновать соответствие созданной схемы поставленной задаче.Темы для проработкиМодель "сущность-связь" (ER-модель).Первичные и внешние ключи.Типы связей и их моделирование.Нормальные формы и нормализация.Требования к схемеСхема должна соответствовать поставленной задаче.Связи между сущностями должны быть правильно смоделированы.Таблицы должны удовлетворять, по крайней мере, третьей нормальной форме.Желательно придерживаться какой-либо системы в именовании таблиц и столбцов.ПримерыВ качестве примера рассмотрим предметную область «Музыкальные предпочтения»:ИсполнительПесняЖанрНазвание1АльбомNОценкаПредпочтениеГородN1ФИОДата11ПолМеломанГодрожденияПервым шагом в проектировании схемы базы данных является определение цели созданиябазы данных.

Зададимся целью создать простую социальную сеть для меломанов, позволяющуюим:Находить людей, которые имеют схожие музыкальные вкусы.Получать от системы рекомендации, какие еще песни, альбомы и исполнители могутим понравиться.Просматривать рейтинг исполнителей, альбомов и композиций.Схема базы данных для этого случая может выглядеть, например, следующим образом:Рассмотрим получившиеся таблицы и некоторые рассуждения, которые привели кприведенной схеме: Таблица User хранит данные о пользователе-меломане. Помимо базовых данных, таких какимя, фамилия, пол, дата рождения и город, эта таблица хранит также адрес электроннойпочты для связи и авторизации в предполагаемой социальной сети (а также хэш пароля). Таблицы City и Country служат справочниками для городов и стран, позволяя избежатьизбыточности и аномалий обновления.12 Таблица Song хранит уникальный идентификатор и название песни.

Таблица Genre являетсясправочником жанров, а таблица SongGenre связывает песни и жанры, моделируя связьтипа «многие-ко-многим», так как одна песня может быть помечена несколькими жанрами,а также каждый жанр может включать несколько песен. Таблица Album хранит информацию об альбомах. Так как одна песня может быть включенав несколько альбомов, а каждый альбом может содержать несколько песен, но требуетсятаблица SongAlbum для организации связи «многие-ко-многим».

Эта таблица также хранитномер трека данной песни в альбоме. Важным столбцом в таблице Album является ссылка на исполнителя, если это альбомодного исполнителя. Если же это альбом-сборник, то ArtistID будет содержатьнеопределенное значение. Таблица Artist хранит данные об исполнителях, включая страну исполнителя. Исполнителисвязаны с песнями опять же с помощью связи «многие-ко-многим» (таблица SongArtist),учитывая случаи, когда у песни есть несколько исполнителей. Наконец, таблица Like хранит предпочтения пользователей: какая песня, когда и насколькопонравилась- пусть Score отражает оценку пользователем песни от 1 («ничегоособенного») до 5 («очень нравится»).13Практическое задание №3. Создание и заполнение таблиц в MS SQLServerПостановка задачиТретье практическое задание заключается в подготовке SQL-скрипта для создания таблицсогласно схеме, полученной в предыдущем задании (с уточнением типов столбцов). Необходимоопределить первичные и внешние ключи, а также декларативные ограничения целостности(возможность принимать неопределенное значение, уникальные ключи, проверочныеограничения и т.

д.). Таблицы следует создавать в отдельной базе данных.Кроме того, нужно подготовить данные для заполнения созданных таблиц. Объемподготовленных данных должен составлять не менее 10 экземпляров для каждой из стержневыхсущностей и 20 экземпляров для каждой из ассоциативных. На основе этих данных необходимосоздать SQL-скрипт для вставки соответствующих строк в таблицы БД.Темы для проработкиЯзык DDL, операторы CREATE TABLE и ALTER TABLE.Типы данных MS SQL Server.Декларативные ограничения целостности.Создание таблиц в среде Microsoft SQL Server Management Studio.Оператор INSERT.ПримерыРассмотрим скрипт для создания схемы базы данных, спроектированной в задании №2:CREATE TABLE [Country]([ID] [int] IDENTITY(1,1) NOT NULL,[Name] [varchar](100) NOT NULL,CONSTRAINT [PK_Country] PRIMARY KEY ([ID] ASC));CREATE TABLE [Song]([ID] [int] IDENTITY(1,1) NOT NULL,[Title] [varchar](200) NOT NULL,CONSTRAINT [PK_Song] PRIMARY KEY ([ID] ASC));CREATE TABLE [Genre]([ID] [int] IDENTITY(1,1) NOT NULL,[Name] [varchar](100) NOT NULL,CONSTRAINT [PK_Genre] PRIMARY KEY ([ID] ASC));CREATE TABLE [Artist]([ID] [int] IDENTITY(1,1) NOT NULL,[Name] [varchar](100) NOT NULL,[CountryID] [int] NOT NULL,CONSTRAINT [PK_Artist] PRIMARY KEY ([ID] ASC)14);CREATE TABLE [City]([ID] [int] IDENTITY(1,1) NOT NULL,[CountryID] [int] NOT NULL,[Name] [varchar](100) NOT NULL,CONSTRAINT [PK_City] PRIMARY KEY ([ID] ASC));CREATE TABLE [Like]([UserID] [int] NOT NULL,[SongID] [int] NOT NULL,[Score] [tinyint] NOT NULL,[Date] [datetime] NOT NULL CONSTRAINT [DF_Like_Date] DEFAULT(getdate()),CONSTRAINT [PK_Like] PRIMARY KEY([UserID] ASC,[SongID] ASC));CREATE TABLE [SongGenre]([SongID] [int] NOT NULL,[GenreID] [int] NOT NULL,CONSTRAINT [PK_SongGenre] PRIMARY KEY([SongID] ASC,[GenreID] ASC));CREATE TABLE [SongArtist]([SongID] [int] NOT NULL,[ArtistID] [int] NOT NULL,CONSTRAINT [PK_SongArtist] PRIMARY KEY([SongID] ASC,[ArtistID] ASC));CREATE TABLE [SongAlbum]([SongID] [int] NOT NULL,[AlbumID] [int] NOT NULL,[TrackNo] [tinyint] NOT NULL,CONSTRAINT [PK_AlbumArtist] PRIMARY KEY([AlbumID] ASC,[SongID] ASC,[TrackNo] ASC));CREATE TABLE [Album]([ID] [int] IDENTITY(1,1) NOT NULL,[Title] [varchar](200) NOT NULL,15[Year] [int] NOT NULL,[ArtistID] [int] NULL,CONSTRAINT [PK_Album] PRIMARY KEY ([ID] ASC));CREATE TABLE [User]([ID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [varchar](100) NOT NULL,[LastName] [varchar](100) NOT NULL,[Sex] [bit] NOT NULL,[BirthDate] [datetime] NOT NULL,[CityID] [int] NOT NULL,[Email] [varchar](100) NOT NULL,[PasswordHash] [varchar](100) NOT NULL,CONSTRAINT [PK_User] PRIMARY KEY ([ID] ASC));CREATE UNIQUE INDEX [IX_User] ON [User]([Email] ASC);CREATE UNIQUE INDEX [IX_Country] ON [Country]([Name] ASC);CREATE UNIQUE INDEX [IX_Genre] ON [Genre]([Name] ASC);ALTER TABLE [Artist]ADD CONSTRAINT [FK_Artist_Country] FOREIGN KEY ([CountryID])REFERENCES [Country] ([ID]);ALTER TABLE [City]ADD CONSTRAINT [FK_City_Country] FOREIGN KEY ([CountryID])REFERENCES [Country] ([ID]);ALTER TABLE [Like]ADD CONSTRAINT [FK_Like_Song] FOREIGN KEY ([SongID])REFERENCES [Song] ([ID]);ALTER TABLE [Like]ADD CONSTRAINT [FK_Like_User] FOREIGN KEY ([UserID])REFERENCES [User] ([ID]);ALTER TABLE [Like]ADD CONSTRAINT [CK_Like_Score]CHECK ([Score] > 0 AND [Score] < 6);ALTER TABLE [SongGenre]ADD CONSTRAINT [FK_SongGenre_Genre] FOREIGN KEY ([GenreID])REFERENCES [Genre] ([ID]);ALTER TABLE [SongGenre]ADD CONSTRAINT [FK_SongGenre_Song] FOREIGN KEY ([SongID])REFERENCES [Song] ([ID]);ALTER TABLE [SongArtist]ADD CONSTRAINT [FK_SongArtist_Artist] FOREIGN KEY ([ArtistID])16REFERENCES [Artist] ([ID]);ALTER TABLE [SongArtist]ADD CONSTRAINT [FK_SongArtist_Song] FOREIGN KEY ([SongID])REFERENCES [Song] ([ID]);ALTER TABLE [SongAlbum]ADD CONSTRAINT [FK_SongAlbum_Album] FOREIGN KEY ([AlbumID])REFERENCES [Album] ([ID]);ALTER TABLE [SongAlbum]ADD CONSTRAINT [FK_SongAlbum_Song] FOREIGN KEY ([SongID])REFERENCES [Song] ([ID]);ALTER TABLE [SongAlbum]ADD CONSTRAINT [CK_SongAlbum_TrackNo]CHECK ([TrackNo] > 0);ALTER TABLE [Album]ADD CONSTRAINT [FK_Album_Artist] FOREIGN KEY ([ArtistID])REFERENCES [Artist] ([ID]);ALTER TABLE [Album]ADD CONSTRAINT [CK_Album_Year]CHECK (([Year] > 1900) AND ([Year] <= YEAR(getdate())));ALTER TABLE [User]ADD CONSTRAINT [FK_User_City] FOREIGN KEY ([CityID])REFERENCES [City] ([ID]);ALTER TABLE [User]ADD CONSTRAINT [CK_User_BirthDate]CHECK (YEAR(getdate()) - YEAR([BirthDate]) >= 7);Приведенный выше скрипт содержит: Определения таблиц, первичных и внешних ключей. Ограничения CHECK: например, указано, что пользователь может оценивать, насколько емунравится песня по шкале от 1 до 5, другие значения Score считаются недопустимыми.

Характеристики

Список файлов книги