Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

2. (30points, each 5 points)Consider the detection relational database for a detection enterprise with 3 relations as follows: project(projectcode, createdate, projectname) projectcode:code of a project,

image text in transcribed

image text in transcribed

2. (30points, each 5 points)Consider the detection relational database for a detection enterprise with 3 relations as follows: project(projectcode, createdate, projectname) projectcode:code of a project, createdate: when the project was created in system; projectname: project name; projectcode is the primary key of this relation. contact(contactcode contactname, amount, startdate , enddate, projectcode), contactcode: contact code: contactname:contact name; amount: the total amount for this project; startdate: the beginning date of this contact; enddate: the enddate of this contact; projectcode:the project code which this contact belongs to is a foreign key sample(samplecode,samplename, tensilevalue, testtime contactcode), samplecode: the sample code; samplename: the sample name; tensilevalue:the tensile value for this sample; testtime: test time for this sample; contactcode: the contact code which this sample belongs to is a foreign key. A project has possibly several contacts and a contact belongs to one sole project. One contact may have several samples and a sample belongs to sole contact. The primary keys are underlined by solid lines. Please give the SQL statements for the following problems: (1) Create table contact with suitable data type for each attribute. You are required to define contactcode as the primary key and projectcode as the foreign key which references to project (projectcode). (2) Retrival the contactname which containing the letter 'v'. (3) Retrival the projectcode and projectname which is created before "2016-01-01". (4) Retrival each project code and its total amount according to contact relation. (5) Retrival sample name, tensilevalue and corresponding contact name which belongs to the contact with code 2020101. (6) Delete all the sample rows which belongs to the contact with code "2020102

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Inference Control In Statistical Databases From Theory To Practice Lncs 2316

Authors: Josep Domingo-Ferrer

2002nd Edition

3540436146, 978-3540436140

More Books

Students also viewed these Databases questions