Database Assignments Sample Assignment
Database assignments
Question 1
Exercise 1:
Describe the element (entities, relationships and attributes) of the table of this database system?
Answer:
Entities:
Works:
Artist:
Visitors:
Relationships:
One to many between Works and Artist Entities:
One to many: between Visitors and Works Entities:
Attributes:
Works: Barcode, description, title
Artist: Name, address, contact number
Visitors: name, address, phone number, barcode
Vote: votes
Exercise 2:
Draw a Er diagram for generated database tables?
E R Diagram:
Relational diagram:
Exercise 3: sql code to write table:
For create Artist table:
CREATE TABLE [dbo].[Artist](
[Artist_Name] [nvarchar](50) NOT NULL,
[Adress] [nvarchar](50) NULL,
[Contact_number] [nvarchar](20) NULL,
CONSTRAINT [PK_Artist] PRIMARY KEY CLUSTERED
(
[Artist_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
For create vistor table:
CREATE TABLE [dbo].[vistor](
[Name] [nvarchar](50) NULL,
[Address] [nvarchar](50) NULL,
[Phone_Number] [nvarchar](50) NULL,
[Vistor_Barcode] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_vistor] PRIMARY KEY CLUSTERED
(
[Vistor_Barcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
For create Vote table:
CREATE TABLE [dbo].[Vote](
[work_Barcode] [nvarchar](20) NOT NULL,
[Vistor_Barcode] [nvarchar](20) NOT NULL,
[Votes] [int] NULL,
CONSTRAINT [PK_Vote] PRIMARY KEY CLUSTERED
(
[work_Barcode] ASC,
[Vistor_Barcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
For create Vote Worker:
CREATE TABLE [dbo].[Worker](
[work_Barcode] [nvarchar](20) NOT NULL,
[Description] [nvarchar](50) NULL,
[Title] [nvarchar](20) NULL,
[Artist_Name] [nvarchar](50) NULL,
CONSTRAINT [PK_Worker] PRIMARY KEY CLUSTERED
(
[work_Barcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
For relationship creation:
ALTER TABLE [dbo].[Vote] WITH CHECK ADD CONSTRAINT [FK_Vote_vistor] FOREIGN KEY([Vistor_Barcode])
REFERENCES [dbo].[vistor] ([Vistor_Barcode])
GO
ALTER TABLE [dbo].[Vote] CHECK CONSTRAINT [FK_Vote_vistor]
GO
ALTER TABLE [dbo].[Vote] WITH CHECK ADD CONSTRAINT [FK_Vote_Worker] FOREIGN KEY([work_Barcode])
REFERENCES [dbo].[Worker] ([work_Barcode])
GO
ALTER TABLE [dbo].[Vote] CHECK CONSTRAINT [FK_Vote_Worker]
GO
ALTER TABLE [dbo].[Worker] WITH CHECK ADD CONSTRAINT [FK_Worker_Artist] FOREIGN KEY([Artist_Name])
REFERENCES [dbo].[Artist] ([Artist_Name])
GO
Question 2
Exercise 2:
Write a query to find all students enrolled into Assignment in 2016 or later?
Answer:
Query:
Select *
FROM student1 as st
inner JOIN Student2 as st2 ON st.Student_id =st2.Student_id
inner JOIN Assignment as cour ON st2.Assignment_Id =cour.Assignment_Id
where year_join <= '2016'
Exercise 3:
Write a query to generate a list of all students who have letter ‘a’ in their name (first name /last name)?
Query:
Select *
FROM student1 as st
inner JOIN Student2 as st2 ON st.Student_id =st2.Student_id
inner JOIN Assignment as cour ON st2.Assignment_Id =cour.Assignment_Id
where st.student_f_name LIKE '%A%' OR st.student_Lname LIKE '%A%'
Exercise 4:
Write a query to find students who enrolled for units offered in ‘Burwood’?
Select *
FROM student1 as st
inner JOIN Student2 as st2 ON st.Student_id =st2.Student_id
inner JOIN Assignment as cour ON st2.Assignment_Id =cour.Assignment_Id
where Location = 'burwood'
Exercise 5:
Write a query to show only to students who have enrolled to ’database’ or a Assignment that has been offered in ‘Geelong?
Query:
Select *
FROM student1 as st
inner JOIN Student2 as st2 ON st.Student_id =st2.Student_id
inner JOIN Assignment as cour ON st2.Assignment_Id =cour.Assignment_Id
where (cour.Location = 'geelong' )or (cour.Assignment ='database')
Question 3:
Exercise 1:
Exercise 2:
Exercise 3:
Exercise 4:
Er diagram exercise 5:
Question 4
Er diagram :
Dependancy diagram:
Code for sql table:
CREATE TABLE [dbo].[Owner](
[Owner_name] [nvarchar](50) NOT NULL,
[Owner_id] [int] NOT NULL,
CONSTRAINT [PK_Owner] PRIMARY KEY CLUSTERED
(
[Owner_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Pet](
[Pet_Id] [int] NOT NULL,
[Pet_name] [nvarchar](50) NULL,
[Pet_Age] [nvarchar](20) NULL,
[Type_Id] [int] NULL,
[Owner_id] [int] NULL,
CONSTRAINT [PK_Pet] PRIMARY KEY CLUSTERED
(
[Pet_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Procedure](
[Procedure_ID] [int] NOT NULL,
[procedure] [nvarchar](50) NULL,
[Visit_Date] [nchar](10) NULL,
[Owner_id] [int] NULL,
CONSTRAINT [PK_Procedure] PRIMARY KEY CLUSTERED
(
[Procedure_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Tyep](
[Pet_type] [nvarchar](20) NULL,
[Type_Id] [int] NOT NULL,
CONSTRAINT [PK_Tyep] PRIMARY KEY CLUSTERED
(
[Type_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Code for relationship between tables:
ALTER TABLE [dbo].[Pet] WITH CHECK ADD CONSTRAINT [FK_Pet_Owner] FOREIGN KEY([Owner_id])
REFERENCES [dbo].[Owner] ([Owner_id])
ALTER TABLE [dbo].[Pet] WITH CHECK ADD CONSTRAINT [FK_Pet_Tyep] FOREIGN KEY([Type_Id])
REFERENCES [dbo].[Tyep] ([Type_Id])
ALTER TABLE [dbo].[Pet] CHECK CONSTRAINT [FK_Pet_Tyep]
ALTER TABLE [dbo].[Procedure] WITH CHECK ADD CONSTRAINT [FK_Procedure_Owner] FOREIGN KEY([Owner_id])
REFERENCES [dbo].[Owner] ([Owner_id])
ALTER TABLE [dbo].[Procedure] CHECK CONSTRAINT [FK_Procedure_Owner]
ALTER DATABASE [petdatabse] SET READ_WRITE
