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 course 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 Course as cour ON st2.Course_Id =cour.Course_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 Course as cour ON st2.Course_Id =cour.Course_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 Course as cour ON st2.Course_Id =cour.Course_Id

where Location = 'burwood'

Exercise 5:

Write a query to show only to students who have enrolled to ’database’ or a course 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 Course as cour ON st2.Course_Id =cour.Course_Id

where (cour.Location = 'geelong' )or (cour.Course ='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