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

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

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

Такжезадается ограничение и на дату рождения пользователя и год выпуска альбома. Значение по умолчанию для столбца Date в таблице Like – текущее время. Ограничения NOT NULL. Столбец ArtistID в таблице Album допускает неопределенныезначения – если у альбома нет определенного исполнителя. Ограничение на столбец Email в таблице User – допускаются только уникальные значения.Аналогичным образом не допускается появления двух стран с одинаковым названием Nameв таблице Country, а также двух жанров с одинаковым названием в таблице Genre.Полезно также подготовить скрипт для удаления таблиц, так как оператор CREATE TABLE вернетошибку, если таблица уже существует:DROPDROPDROPDROPDROPDROPDROPDROPDROPTABLETABLETABLETABLETABLETABLETABLETABLETABLE[SongGenre];[SongAlbum];[SongArtist];[Like];[Album];[Song];[Artist];[User];[City];17DROP TABLE [Country];DROP TABLE [Genre];Обратите внимание, что сначала нужно удалять таблицы, на которые нет ссылок из другихтаблиц, иначе сработают ограничения ссылочной целостности.Теперь рассмотрим заполнение базы данных модельным содержимым в соответствии спостановкой задачи.

SQL-скрипт для этого может выглядеть, например, так (обратите внимание напредварительную очистку таблиц и правильный порядок заполнения - чтобы не нарушалисьограничения ссылочной целостности):DELETEDELETEDELETEDELETEDELETEDELETEDELETEDELETEDELETEDELETEDELETEFROMFROMFROMFROMFROMFROMFROMFROMFROMFROMFROM[SongAlbum][Album][SongArtist][SongGenre][Like][User][City][Artist][Genre][Song][Country]SET IDENTITY_INSERT [Country] ONINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESINSERT [Country] ([ID], [Name]) VALUESSET IDENTITY_INSERT [Country] OFFSET IDENTITY_INSERT [Song] ONINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESINSERT [Song] ([ID], [Title]) VALUESSET IDENTITY_INSERT [Song] OFFSET IDENTITY_INSERT [Genre] ONINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUES(1, 'Germany')(2, 'Great Britain')(3, 'USA')(4, 'Russia')(5, 'Canada')(6, 'Finland')(7, 'Sweden')(8, 'Japan')(9, 'Korea')(10, 'Ukrain')(11, 'Switzerland')(12, 'France')(13, 'Italy')(14, 'Poland')(15, 'Greece')(16, 'Turkey')(17, 'Estonia')(18, 'Latvia')(19, 'Romania')(20, 'Brazil')(1, 'Drifting Sun')(2, 'Fly to the Rainbow')(3, 'Dark Lady')(4, 'In Trance')(5, 'Life is Like A River')(6, 'I Saw Her Standing There')(7, 'Misery')(8, 'Anna (Go to Him)')(9, 'Chains')(10, 'Boys')(11, 'Good Times, Bad Times')(12, 'Babe I’m Gonna Leave You')(13, 'You Shook Me')(14, 'Dazed And Confused')(15, 'Your Time Is Gonna Come')(16, 'Keep Yourself Alive')(17, 'Doing Alright')(18, 'Great King Rat')(19, 'My Fairy King')(20, 'Liar')(1,(2,(3,(4,'Heavy Metal')'Hard Rock')'Rock-n-Roll')'Blues Rock')18INSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESINSERT [Genre] ([ID], [Name]) VALUESSET IDENTITY_INSERT [Genre] OFF(5, 'Blues')(6, 'Rock')(7, 'Punk')(8, 'Punk Rock')(9, 'Metal')(10, 'Progressive Metal')(11, 'Pop')(12, 'Classical')(13, 'Symphonic Metal')(14, 'Drum-n-Bass')(15, 'Club')(16, 'Electro')(17, 'Folk')(18, 'Folk Metal')(19, 'Folk Rock')(20, 'Pop Punk')SET IDENTITY_INSERT [Artist] ONINSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])INSERT [Artist] ([ID], [Name], [CountryID])SET IDENTITY_INSERT [Artist] OFFSET IDENTITY_INSERT [City] ONINSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],INSERT [City] ([ID], [CountryID],SET IDENTITY_INSERT [City] OFF[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])[Name])VALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUES(1, 'The Scorpions', 1)(2, 'The Beatles', 2)(3, 'Led Zeppelin', 2)(4, 'Queen', 2)(5, 'Black Sabbath', 2)(6, '3 Doors Down', 3)(7, 'The Offspring', 3)(9, 'Madonna', 3)(10, 'The Animals', 2)(11, 'Pink Floyd', 2)(12, 'Deep Purple', 2)(13, 'Jimi Hendrix', 3)(14, 'The Who', 2)(15, 'Aerosmith', 3)(16, 'The Police', 2)(18, 'Eagles', 3)(19, 'System Of A Down', 3)(22, 'The Misfits', 3)(23, 'Elvis Presley', 3)(1, 1, 'Berlin')(2, 2, 'Londo')(3, 3, 'New York')(4, 3, 'Washington')(5, 3, 'San-Francisco')(6, 4, 'Moscow')(7, 4, 'St.

Petersburg')(8, 5, 'Vancouver')(9, 6, 'Helsinki')(10, 7, 'Stockholm')(11, 8, 'Tokio')(12, 9, 'Seoul')(13, 10, 'Kiev')(15, 11, 'Bern')(16, 12, 'Paris')(17, 13, 'Rome')(18, 14, 'Warsaw')(19, 15, 'Athens')(20, 16, 'Ankara')SET IDENTITY_INSERT [User] ONINSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (1, 'John', 'Smith', 1, convert(datetime, '1982-03-24', 102), 5,'john@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (2, 'Mike', 'Johnson', 1, convert(datetime, '1982-04-14', 102), 4,'mike@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (3, 'Alice', 'Robertson', 0, convert(datetime, '1985-03-17', 102), 4,'alice@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (4, 'Mary', 'Richards', 0, convert(datetime, '1983-08-21', 102), 5,'mary@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (5, 'Jack', 'Dare', 1, convert(datetime, '1981-08-16', 102), 3,'jack@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],19[PasswordHash]) VALUES (7, 'Tommy', 'Anderson', 1, convert(datetime, '1989-07-11', 102), 3,'tommy@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (9, 'Janet', 'Thomson', 0, convert(datetime, '1992-09-30', 102), 4,'janet@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (10, 'Rick', 'Jackson', 1, convert(datetime, '1982-09-19', 102), 3,'rick@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (12, 'Joanna', 'Rosenberg', 0, convert(datetime, '1993-05-13', 102), 5,'joanna@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (14, 'Jill', 'Jordan', 0, convert(datetime, '1995-04-10', 102), 4,'jill@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (15, 'Ivan', 'Petrov', 1, convert(datetime, '1987-03-22', 102), 6,'ivan@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (16, 'Sergey', 'Ivanov', 1, convert(datetime, '1988-05-25', 102), 7,'sergey@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (18, 'Petr', 'Sidorov', 1, convert(datetime, '1988-03-25', 102), 6,'petr@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (19, 'Anna', 'Ivanova', 0, convert(datetime, '1987-04-21', 102), 7,'anna@mail.com', '')INSERT [User] ([ID], [FirstName], [LastName], [Sex], [BirthDate], [CityID], [Email],[PasswordHash]) VALUES (20, 'Katerina', 'Petrova', 0, convert(datetime, '1981-09-12', 102), 7,'katerina@mail.com', '')SET IDENTITY_INSERT [User] OFFINSERT [Like]24', 102))INSERT [Like]02', 102))INSERT [Like]13', 102))INSERT [Like]15', 102))INSERT [Like]07', 102))INSERT [Like]10', 102))INSERT [Like]14', 102))INSERT [Like]01', 102))INSERT [Like]10', 102))INSERT [Like]14', 102))INSERT [Like]24', 102))INSERT [Like]06', 102))INSERT [Like]11', 102))INSERT [Like]17', 102))INSERT [Like]08', 102))INSERT [Like]14', 102))INSERT [Like]05', 102))INSERT [Like]14', 102))INSERT [Like]24', 102))INSERT [Like]25', 102))INSERTINSERTINSERTINSERTINSERTINSERTINSERT([UserID], [SongID], [Score], [Date]) VALUES (1, 1, 4, convert(datetime, '2010-06([UserID], [SongID], [Score], [Date]) VALUES (1, 4, 5, convert(datetime, '2010-07([UserID], [SongID], [Score], [Date]) VALUES (1, 7, 5, convert(datetime, '2010-07([UserID], [SongID], [Score], [Date]) VALUES (1, 14, 3, convert(datetime, '2010-08([UserID], [SongID], [Score], [Date]) VALUES (2, 3, 3, convert(datetime, '2010-09([UserID], [SongID], [Score], [Date]) VALUES (2, 6, 1, convert(datetime, '2010-10([UserID], [SongID], [Score], [Date]) VALUES (2, 18, 4, convert(datetime, '2010-11([UserID], [SongID], [Score], [Date]) VALUES (3, 4, 4, convert(datetime, '2010-12([UserID], [SongID], [Score], [Date]) VALUES (3, 11, 5, convert(datetime, '2010-12([UserID], [SongID], [Score], [Date]) VALUES (3, 12, 5, convert(datetime, '2011-01([UserID], [SongID], [Score], [Date]) VALUES (3, 18, 5, convert(datetime, '2011-03([UserID], [SongID], [Score], [Date]) VALUES (3, 19, 5, convert(datetime, '2011-04([UserID], [SongID], [Score], [Date]) VALUES (4, 5, 4, convert(datetime, '2011-05([UserID], [SongID], [Score], [Date]) VALUES (4, 7, 1, convert(datetime, '2011-06([UserID], [SongID], [Score], [Date]) VALUES (4, 13, 5, convert(datetime, '2011-09([UserID], [SongID], [Score], [Date]) VALUES (4, 16, 4, convert(datetime, '2011-10([UserID], [SongID], [Score], [Date]) VALUES (5, 3, 5, convert(datetime, '2012-03([UserID], [SongID], [Score], [Date]) VALUES (5, 8, 5, convert(datetime, '2012-05([UserID], [SongID], [Score], [Date]) VALUES (5, 14, 3, convert(datetime, '2012-06([UserID], [SongID], [Score], [Date]) VALUES (7, 10, 5, convert(datetime, '2012-06-[SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre]([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])VALUESVALUESVALUESVALUESVALUESVALUESVALUES(1,(1,(2,(2,(3,(3,(4,201)2)1)2)1)2)1)INSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERT[SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre][SongGenre]([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],INSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERT[SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][SongArtist][GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])[GenreID])([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],SET IDENTITY_INSERT [Album] ONINSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],INSERT [Album] ([ID], [Title],VALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUES[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],[Year],(4, 2)(5, 1)(5, 2)(6, 3)(7, 3)(8, 3)(9, 3)(10, 3)(11, 2)(11, 4)(12, 2)(12, 4)(13, 2)(13, 4)(14, 2)(14, 4)(15, 2)(15, 4)(16, 1)(16, 2)(17, 1)(17, 2)(18, 1)(18, 2)(19, 1)(19, 2)(20, 1)(20, 2)VALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUES(1, 1)(2, 1)(3, 1)(4, 1)(5, 1)(6, 2)(7, 2)(8, 2)(9, 2)(10, 2)(11, 3)(12, 3)(13, 3)(14, 3)(15, 3)(16, 4)(17, 4)(18, 4)(19, 4)(20, 4)[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])[ArtistID])21VALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUES(1, 'Lonesome Crow', 1972, 1)(2, 'Fly to the Rainbow', 1974, 1)(3, 'In Trance', 1975, 1)(4, 'Virgin Killer', 1976, 1)(6, 'Taken by Force', 1977, 1)(7, 'Lovedrive', 1979, 1)(8, 'Animal Magnetism ', 1980, 1)(9, 'Blackout', 1982, 1)(10, 'Love at First Sting', 1984, 1)(11, 'Savage Amusement', 1988, 1)(12, 'Please Please Me', 1963, 2)(13, 'With The Beatles', 1963, 2)(14, 'A Hard Day’s Night', 1964, 2)(15, 'Beatles For Sale', 1964, 2)(16, 'Help!', 1965, 2)(17, 'Rubber Soul', 1965, 2)(18, 'Revolver', 1966, 2)(19, 'Led Zeppelin', 1969, 3)(20, 'Led Zeppelin II', 1969, 3)(21, 'Led Zeppelin III', 1970, 3)(24, 'Queen', 1973, 4)(25, 'Queen II', 1974, 4)(26, 'Black Sabbath', 1970, 5)(27, 'Paranoid', 1970, 5)(28, 'The Better Life', 2000, 6)(29, 'Away from the Sun', 2002, 6)(30, 'Seventeen Days', 2005, 6)(31, 'The Offspring', 1989, 7)INSERT [Album] ([ID], [Title], [Year], [ArtistID]) VALUES (32, 'Ignition', 1982, 7)INSERT [Album] ([ID], [Title], [Year], [ArtistID]) VALUES (34, 'Madonna', 1983, 9)INSERT [Album] ([ID], [Title], [Year], [ArtistID]) VALUES (35, 'The Animals', 1964, 10)SET IDENTITY_INSERT [Album] OFFINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERTINSERT[SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum][SongAlbum]([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],([SongID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[AlbumID],[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])[TrackNo])VALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUESVALUES(1, 2, 3)(2, 2, 7)(3, 3, 1)(4, 3, 2)(5, 3, 3)(6, 12, 1)(7, 12, 2)(8, 12, 3)(9, 12, 4)(10, 12, 5)(11, 19, 1)(12, 19, 2)(13, 19, 3)(14, 19, 4)(15, 19, 5)(16, 24, 1)(17, 24, 2)(18, 24, 3)(19, 24, 4)(20, 24, 5)Примеры вопросов по обязательной частиОбъяснить, что делают написанные запросы.В чем различие типов CHAR и VARCHAR? VARCHAR и NVARCHAR?Что такое внешний ключ?Какие существуют способы поддержания ссылочной целостности?Что такое уникальный ключ?Что такое нормализация?Рассказать о нормальных формах.Что такое IDENTITY?Рассказать о значениях по умолчанию и неопределенных значениях.Рассказать о вычисляемых столбцах.Как можно представить значение булевского типа?Как можно хранить даты и время?Рассказать о числовых типах данных.Каким образом можно вставить несколько строк с помощью одного оператора INSERT?Как ведет себя оператор INSERT, если в списке столбцов перечислены не все столбцы?Примеры дополнительных вопросов Добавить какие-либо ограничения целостности. Добавить IDENTITY. Исправить выявленные при проверке недочеты.22Практическое задание №4.

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

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