In this problem you\'ll write the sql to move data from the 1NF table STUDENT_CL

Question

In this problem you'll write the sql to move data from the 1NF table STUDENT_CLASS to normalized tables
and check the integrity constraints

Here are the relations represented by the tables

STUDENT_CLASS(StudentId, ClassId, StudentName, ClassDesc)

has the functional and multivalued dependencies:

Student --> StudentName

Student -->--> (ClassId, ClassDesc)

These can be normalized (BCNF and 4NF) by replacing STUDENT_CLASS with three relations:

STUDENT(StudentId(PK), StudentName)

CLASS(ClassId(PK), ClassDesc)

STUDENT_CLASS2(StudentId(PK), ClassId(PK))

FK:StudentId --> STUDENT.StudentId

FK:ClassId --> CLASS.ClassId

Referential Integrity Constraints:

STUDENT_CLASS2.StudentId must exist in STUDENT.StudentId

STUDENT_CLASS2.ClassId must exist in CLASS.ClassId

In what follows the physical tables have DMLab2_ prefixed

to the relation name

*/

-- you might need to change the name below to your database name

USE DMLab2EnrollmentDB;

GO

-- Problem 1:

-- Insert rows into DMLab2_STUDENT

-- by selecting the distinct StudentId and StudentName

-- from DMLab2_STUDENT_CLASS

--

-- You don't need to format or alias columns or put each column

-- on a separate line for this lab only.

---

GO

SELECT * FROM DMLab2_STUDENT;

GO

-- Problem 2:

-- Insert rows into DMLab2_CLASS

-- by selecting the distinct ClassId and ClassDesc

-- from DMLab2_STUDENT_CLASS

GO

SELECT * FROM DMLab2_CLASS;

GO

-- Problem 3:

-- Insert rows into DMLab2_STUDENT_CLASS2

-- by selecting StudentId and ClassId

-- from DMLab2_STUDENT_CLASS

--

GO

SELECT * FROM DMLab2_STUDENT_CLASS2;

GO

-- Problem 4:

-- Write a query to check the referential integrity constraint:

-- DMLab2_STUDENT_CLASS2.StudentId must exist in DMLab2_STUDENT.StudentId

--

GO

-- Problem 5:

-- Write a query to check the referential integrity constraint:

-- DMLab2_STUDENT_CLASS2.ClassId must exist in DMLab2_CLASS.ClassId

--

GO

****BELOW IS THE DMLAB2ENROLLMENTDB.SQL***

USE master

GO

CREATE Database DMLab2EnrollmentDB

GO

USE DMLab2EnrollmentDB

CREATE TABLE [dbo].[DMLab2_CLASS](

[ClassId] [char](10) NOT NULL,

[ClassDesc] [varchar](35) NULL,

CONSTRAINT [PK_DMLab2_CLASS] PRIMARY KEY CLUSTERED

(

[ClassId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[DMLab2_STUDENT] Script Date: 4/22/2012 10:38:38 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[DMLab2_STUDENT](

[StudentId] [int] NOT NULL,

[StudentName] [varchar](35) NULL,

CONSTRAINT [PK_DMLab2_STUDENT] PRIMARY KEY CLUSTERED

(

[StudentId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[DMLab2_STUDENT_CLASS] Script Date: 4/22/2012 10:38:38 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[DMLab2_STUDENT_CLASS](

[StudentId] [int] NULL,

[ClassId] [char](10) NULL,

[StudentName] [varchar](35) NULL,

[ClassDesc] [varchar](35) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[DMLab2_STUDENT_CLASS2] Script Date: 4/22/2012 10:38:38 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[DMLab2_STUDENT_CLASS2](

[StudentId] [int] NOT NULL,

[ClassId] [char](10) NOT NULL,

CONSTRAINT [PK_DMLab2_STUDENT_CLASS2] PRIMARY KEY CLUSTERED

(

[StudentId] ASC,

[ClassId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[DMLab2_STUDENT_CLASS] ([StudentId], [ClassId], [StudentName], [ClassDesc]) VALUES (1, N'cis122 ', N'c dobbins', N'software design')

INSERT [dbo].[DMLab2_STUDENT_CLASS] ([StudentId], [ClassId], [StudentName], [ClassDesc]) VALUES (2, N'cis275 ', N'mary smith', N'Data Modeling and SQL')

INSERT [dbo].[DMLab2_STUDENT_CLASS] ([StudentId], [ClassId], [StudentName], [ClassDesc]) VALUES (2, N'cis233b ', N'mary smith', N'Visual Basic II')

INSERT [dbo].[DMLab2_STUDENT_CLASS] ([StudentId], [ClassId], [StudentName], [ClassDesc]) VALUES (3, N'cis234n ', N'fraz daz', N'C# Programming III')

INSERT [dbo].[DMLab2_STUDENT_CLASS] ([StudentId], [ClassId], [StudentName], [ClassDesc]) VALUES (3, N'cis275 ', N'fraz daz', N'Data Modeling and SQL')

INSERT [dbo].[DMLab2_STUDENT_CLASS] ([StudentId], [ClassId], [StudentName], [ClassDesc]) VALUES (4, N'cis234n ', N'fran dan', N'C# Programming III')

ALTER TABLE [dbo].[DMLab2_STUDENT_CLASS2] WITH CHECK ADD CONSTRAINT [FK_DMLab2_STUDENT_CLASS2_DMLab2_CLASS] FOREIGN KEY([ClassId])

REFERENCES [dbo].[DMLab2_CLASS] ([ClassId])

GO

ALTER TABLE [dbo].[DMLab2_STUDENT_CLASS2] CHECK CONSTRAINT [FK_DMLab2_STUDENT_CLASS2_DMLab2_CLASS]

GO

ALTER TABLE [dbo].[DMLab2_STUDENT_CLASS2] WITH CHECK ADD CONSTRAINT [FK_DMLab2_STUDENT_CLASS2_DMLab2_STUDENT] FOREIGN KEY([StudentId])

REFERENCES [dbo].[DMLab2_STUDENT] ([StudentId])

GO

ALTER TABLE [dbo].[DMLab2_STUDENT_CLASS2] CHECK CONSTRAINT [FK_DMLab2_STUDENT_CLASS2_DMLab2_STUDENT]

GO

Explanation / Answer

--Problem 1:
Insert into DMLab2_STUDENT
select distinct StudentId , StudentName from DMLab2_STUDENT_CLASS

GO
SELECT * FROM DMLab2_STUDENT;
GO
-- Problem 2:
Insert into DMLab2_CLASS
select distinct ClassId , ClassDesc from DMLab2_STUDENT_CLASS

GO
SELECT * FROM DMLab2_CLASS;
GO

-- Problem 3:

Insert into DMLab2_STUDENT_CLASS2
select StudentId , ClassId from DMLab2_STUDENT_CLASS

GO
SELECT * FROM DMLab2_STUDENT_CLASS2;
GO

-- Problem 4:
--Write a query to check the referential integrity constraint:
select * from DMLab2_STUDENT where StudentId in (select StudentId from DMLab2_STUDENT_CLASS2)
GO

-- Problem 5:
-- Write a query to check the referential integrity constraint:
select * from DMLab2_CLASS where ClassId in (select ClassId from DMLab2_STUDENT_CLASS2)

GO

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.