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

Improve Your Grades with Custom Writing Help
Homework Help
Writing Help
Editing Services
Plagiarism check
Proofreading services
Research Project help
Custom writing services
scanner
E learning blogs

Disclaimer : The study tools and academic assistance/guidance through online tutoring sessions provided by AssignmentHelp.Net is to help and enable students to compete academically. The website does not provide ghostwriting services and has ZERO TOLERANCE towards misuse of the services. In case any user is found misusing our services, the user's account will be immediately terminated.