StudentShare solutions
Triangle menu

Database - Assignment Example

Not dowloaded yet

Extract of sample
Database

SQL Code:
SELECT product_no, plant_name FROM product WHERE plant_name LIKE '%rose%'

ii.) List the product numbers and plant names of any products where a quantity of 20 or more has been ordered on a single order.

SQL Code:
SELECT product.product_no, product.plant_name, order_line.qty_ordered FROM product, order_line WHERE order_line.qty_ordered =>20

iii.) List the names and addresses of any customers who have ordered plants that like to grow on 'clay soil'.

SQL Code:
SELECT customer.name, customer.address, product.soil_type FROM customer, product WHERE product.soil_type = 'clay soil'

iv.) List the order no's and order dates of any orders that have been placed for more than 10 different products.

SQL Code:
SELECT order.order_no, order.order_date, order_line.qty_ordered, order_line.order_no FROM order, order_line WHERE order.order_no = order_line.order_no AND order_line.qty_ordered > 10

v.) List the names and addresses of those customers where the average value of their orders is more than twice the average value of all customer orders.

SQL Code:
SELECT customer.name, customer.address, AVG(order.order_total) as total FROM customer, order WHERE order.order_total > (2*total)
Question 3:
The Kennel Club database includes the following schema:

Member (KC_memb_no {pk}, Name, Address, Tel_num)
Show (Show_name {pk}, Year {pk}, Venue)
Dog (KC_reg_no {pk}, Dog_name, Breed_name, KC_memb_no)
Entry (Show_name {pk}, KC_reg_no {pk}, Year {pk}, final_position)

Primary key attributes are indicated with {pk}. Notes - final_position is a numeric attribute that indicates where the dog was placed in the show, i.e. 1 = 1st place. Specify the following queries in relational algebra:

i.)...
Inspectors have a unique inspector number, and the details of their name and position are held on record. Products are identified by their name and records of the product type and a description of the product are kept.
Primary key attributes are indicated with {pk}. N.B. All dates are stored in the format number(6) to represent YYMMDD, i.e. the date 16th June 2005 will be stored as 050616. Selling_price is stored in the format number (4, 2). Quantities are stored in the format number (4). All other attributes are stored as char (). The order_total is the total value of the order. The qty_ordered is the quantity of a particular product ordered. Soil_type refers to the favoured growing conditions of the plant, e.g. 'sandy soil'. Specify the following queries in SQL:
Primary key attributes are indicated with {pk}. Notes - final_position is a numeric attribute that indicates where the dog was placed in the show, i.e. 1 = 1st place. Specify the following queries in relational algebra:
PROJECTDog.Dog_name, Dog.KC_reg_no, Entry.Show_name, Entry.Year, Entry.final_position (SELECT Entry.Show_name = 'Top Dog' and Entry.Year=2002 and Entry.Year =2003 group by Entry.final_position (Dog X Entry)) ...Show more

Summary

Consider the following requirements for the maintenance of computer hardware in an organisation. Each significant piece of computer hardware, such as a server or a printer, is known as an asset, with a unique asset number. For each asset the product name, such as the HP X1 Turbo, its location and the asset's lease renewal date have to be recorded…
Author : runtearlene
Database essay example
Read Text Preview
Save Your Time for More Important Things
Let us write or edit the assignment on your topic
"Database"
with a personal 20% discount.
Grab the best paper

Related Essays

Database Systems
In these distributed databases systems, requests and transactions can further categorised as remote and distributed. Requests can be categorise as remote requests and distributed requests. A remote request accesses data located in a single remote data processor site and queries can accesses that locations data only.
1 pages (250 words) Assignment
Database Configurations
The basic purpose for any database in an organization is to serve the purpose of storing transactions and being able to produce reports effectively (Rob & Coronel, 2002). Although, most database analysts would agree with this simplistic definition of a database, there is more to uses of a database than what meets the eye in this definition.
1 pages (250 words) Assignment
Relational Database Assignment
The four prime factors that control the integrity of a database include relationship-level integrity, attribute-level integrity, business rules, and entity-level integrity (Lathem, 2008). Using these relationships, one can divide the data in separate tables.
3 pages (750 words) Assignment
Database
The second layer is the schema of a given database. The third layer is the database itself representing a state of the application's data often called micro-data. The fourth layer consists of the macro-data that are generated from the micro-data by application of view queries to the micro-data (DIETRICH, 2011).
6 pages (1500 words) Assignment
Database assignment
The concerned techniques used for data modeling and the accompanying tools allows for input, translation and conversion of various system designs (both complex and simple) into processes and data flow representations which are easily read and understood .
10 pages (2500 words) Assignment
Database Management System Assignment
A randomly generated test script is utilized to provide variation among test cases and consistency between different runs of the benchmark. The benchmark is platform independent and can be extended to support any database vendor providing a Java data source for connectivity.
19 pages (4750 words) Assignment
Relational Database Design
The Relational Database specification required nine table in the following relationships. A table Textbook with a many-to-many relationship through a junction table (outside join) to the table Module through the Module_Detail table. A many-to-many relationship is required between Module and Field through a join table (outer join or junction table) Field_Detail.
6 pages (1500 words) Assignment
Database
Every table must have a candidate key. Data that is in a table of the database for a lifetime determines whether attributes have the candidate key or not. A primary key can either be a substitute or surrogate. Foreign tables may contain attributes with external keys
1 pages (250 words) Assignment
Database Management
The growth rate of the following town has seen the apartments booked and the contract is agreed upon with the tenant. The apartments are well furnished with the best quality products and latest fittings
6 pages (1500 words) Assignment
Oracle Database 11g
The database constitutes both logical and physical structures. Since the logical and physical structures are separate, the data physical storage can be managed without necessarily affecting logical storage structure access. Every database instance is
16 pages (4000 words) Assignment
Get a custom paper written
by a pro under your requirements!
Win a special DISCOUNT!
Put in your e-mail and click the button with your lucky finger
Your email
YOUR PRIZE:
Apply my DISCOUNT
Comments (0)
Rate this paper:
Thank you! Your comment has been sent and will be posted after moderation