Todd Clark

1

• Todd Clark
• Bret Birdsong
• Bradley Griffith
• Shane Brown
• Jennifer Broadhurst
• Alan Clark

2

• Founded by Bill Crutchfield on Mothers Kitchen
  Table in 1974

3
(No Transcript)
4
(No Transcript)
5
(No Transcript)
6
(No Transcript)
7
(No Transcript)
8
(No Transcript)
9
(No Transcript)
10
(No Transcript)
11
(No Transcript)
12
MS Access
13
Crutchfield Database
14
Amplifiers Table
15
Amplifier Query
16
Query Output
17
Speakers Table
18
Speakers Query
19
Query Output
20
Customer Table
21
Customer Query
22
Query Output
23
SQL
24
Create Table

• SQL CREATE TABLE AMPLIFIERS_100
• 2 (ITEM_NUM CHAR(15),
• 3 PRICE DECIMAL(6,2),
• 4 MANUFACTURER CHAR(15),
• 5 CHANNELS CHAR(2),
• 6 RMS_POWER_OUTPUT CHAR(5),
• 7 LENGTH CHAR(2),
• 8 HEIGHT CHAR(2),
• 9 WIDTH CHAR(2))
• Table created.

25
Insert into Amplifiers_100

• SQL INSERT INTO AMPLIFIERS_100
• 2 VALUES ('GM-X574','249.99','Pioneer','4','50
  4','9','3','9')
• 1 row created.
• SQL INSERT INTO AMPLIFIERS_100
• 2 VALUES ('XM-460GTX','199.99','Sony','4','604
  ','9','3','9')
• 1 row created.
• SQL INSERT INTO AMPLIFIERS_100
• 2 VALUES ('440SX','109.99','Profile','4','504'
  ,'9','3','13')
• 1 row created.
• SQL INSERT INTO AMPLIFIERS_100
• 2 VALUES ('A461Q','499.99','Auidiobahn','4','75
  4','9','3','12')
• 1 row created.
• SQL INSERT INTO AMPLIFIERS_100
• 2 VALUES ('A6601Q','649.99','Audiobahn','6','75
  6','9','3','12')
• 1 row created.
• SQL INSERT INTO AMPLIFIERS_100
• 2 VALUES ('CA440','129.99','Profile','4','604'
  ,'9','3','11')
• 1 row created.

26
Select
SQL SELECT 2 FROM AMPLIFIERS_100 ITEM_NUM
PRICE MANUFACTURER CH RMS_P LE HE
WI --------------- --------- --------------- --
----- -- -- -- GM-X574 249.99 Pioneer
4 504 9 3 9 XM-460GTX 199.99
Sony 4 604 9 3 9 440SX
109.99 Profile 4 504 9 3 13 A461Q
499.99 Auidiobahn 4 754 9 3
12 A6601Q 649.99 Audiobahn 6
756 9 3 12 CA440 129.99 Profile
4 604 9 3 11 Fosgate Punch
199.99 Rockford 4 374 10 3 9 Fosgate
Punch 219.99 Rockford 4 374 9 3
9 GM-X374 179.99 Pioneer 4
354 10 3 9 JXP680 189.99 Jensen
4 604 9 3 11 KAC-6401 179.99
Kenwood 4 304 9 3 11 KAC-649S
179.99 Kenwood 4 304 9 3
10 KX300.4 279.99 Kicker 4
384 9 3 9 MRP-F200 149 Alpine
4 404 9 3 11 MRP-F320 199.99
Alpine 4 404 9 3 10 Thunder4244
219.99 MTX 4 384 9 3
9 Thunder6304 199.99 MTX 4
384 11 3 10 USB-4065 129.99 US
Acoustics 4 654 9 3 13 USB-4085
179.99 US Acoustics 6 854 9 3 10 ZX460
199.99 Kicker 4 304 9 2
10 20 rows selected.
27
Queries

• SQL SELECT
• 2 FROM AMPLIFIERS_100
• 3 WHERE PRICE
• ITEM_NUM PRICE MANUFACTURER CH
  RMS_P LE HE WI
• --------------- --------- --------------- --
  ----- -- -- --
• XM-460GTX 199.99 Sony 4 604
  9 3 9
• 440SX 109.99 Profile 4 504
  9 3 13
• CA440 129.99 Profile 4 604
  9 3 11
• Fosgate Punch 199.99 Rockford 4 374
  10 3 9
• GM-X374 179.99 Pioneer 4 354
  10 3 9
• JXP680 189.99 Jensen 4 604
  9 3 11
• KAC-6401 179.99 Kenwood 4 304
  9 3 11
• KAC-649S 179.99 Kenwood 4 304
  9 3 10
• MRP-F200 149 Alpine 4 404
  9 3 11
• MRP-F320 199.99 Alpine 4 404
  9 3 10
• Thunder6304 199.99 MTX 4 384
  11 3 10
• USB-4065 129.99 US Acoustics 4 654
  9 3 13
• USB-4085 179.99 US Acoustics 6 854
  9 3 10

28
Queries

• SQL SELECT
• 2 FROM AMPLIFIERS_100
• 3 WHERE LENGTH '9'
• 4 AND HEIGHT '3'
• 5 AND WIDTH '11'
• ITEM_NUM PRICE MANUFACTURER CH
  RMS_P LE HE WI
• --------------- --------- --------------- --
  ----- -- -- --
• CA440 129.99 Profile 4 604
  9 3 11
• JXP680 189.99 Jensen 4 604
  9 3 11
• KAC-6401 179.99 Kenwood 4 304
  9 3 11
• MRP-F200 149 Alpine 4 404
  9 3 11

29
Queries

• SQL SELECT MANUFACTURER
• 2 FROM AMPLIFIERS_100
• 3 WHERE RMS_POWER_OUTPUT '604'
• 4 AND PRICE
• MANUFACTURER
• ---------------
• Sony
• Profile
• Jensen

30
Queries

• SQL SELECT WIDTH, COUNT()
• 2 FROM AMPLIFIERS_100
• 3 GROUP BY WIDTH
• WI COUNT()
• -- ---------
• 10 5
• 11 4
• 12 2
• 13 2
• 9 7

31
Queries

• SQL SELECT PRICE, MANUFACTURER
• 2 FROM AMPLIFIERS_100
• 3 WHERE PRICE
• 4 AND MANUFACTURER 'Pioneer'
• PRICE MANUFACTURER
• --------- ---------------
• 249.99 Pioneer
• 179.99 Pioneer

32

• Crutchfield.com
• Some of the techniques used during the
  semester for this team project can be found
  mostly in Chapters 10 12. Chapter 10 covers
  Internet Database Environment which correspond
  the greatest with our team project seeing as how
  Crutchfield.com is an online merchant. The
  Internet environment includes a network that
  connects the customer workstations, their web
  servers, a database server, following TCP/IP
  protocols. A request from customers is sent
  through the network to the Web server. If the
  request requires that data be obtained from a
  database, the Web server constructs a query and
  sends it to the database server, which processes
  the query and returns the results set. Also
  Crutchfield.com uses firewalls to limit external
  access to the company data. To make the web site
  more convenient to its customers, a shopping cart
  application has been included which is also
  discussed in Chapter 10.

33

• Other issues discussed in Chapter 10 that are
  applicable to Crutchfield.com is security,
  privacy, and handling the increasingly rapid rate
  of change in both business and technology
  practices. Security measures must be placed at
  the network level, operating system level, Web
  server level, database level. Security is also
  applicable to Crutchfield.coms customers.
  Customers must guard their privacy rights, and
  need to be aware of the privacy implications of
  the tools they are using. Chapter 10 also
  discusses the rate of change in which businesses
  like Crutchfield.com must evolve with to keep
  them on the forefront of this technological
  change. Chapter 12 stresses the importance of
  managing data. The functions of
  Crutchfield.coms data administration team, which
  is responsible for the overall management of data
  resources, include developing procedures to
  protect and control data, resolving data
  ownership and the issues, and developing and
  maintaining corporate-wide data definitions and
  standards.

34

• The functions of database administration are
  pretty much the same as those associated with the
  direct database management including DBMS
  installation and upgrading, database design and
  technical issues such as security enforcement,
  database performance, and backup and recovery.
  Crutchfield.coms database administration roles
  are changing in todays business environment,
  which pressure being exerted to maintain data
  quality which building high-performance systems
  quickly. Crutchfield.com has to worry about
  accidental loses, theft and fraud, loss of
  privacy, loss of data integrity, and loss of
  availability which are again, covered in Chap 12.

35
Summary and Conclusions

• Crutchfield is an online retailer of car audio
  equipment. They use a centralized database to
  facilitate web transactions.
• We focused on the amplifiers, speakers, and head
  units that Crutchfield sells. They sell many
  other products that we did not discuss. These
  products provide a good example of the database
  structure for Crutchfield.
• We have given a brief description of the database
  that we selected, an overview of the ER and EER
  diagrams, Normalization, MS Access, and ORACLE.

36
Brets Question

• What would be the Microsoft Access query that
  would display all speakers manufactured by
  Clarion and their prices?

37
Todds Question

• In what way could Crutchfield.com use multiple
  table queries to benefit them?

38
Shanes Question

• In the group two Crutchfield table
  'AMPLIFIERS_100', how would you show in SQL, all
  the amplifiers that was manufactured by Kenwood,
  has the length of nine, the height of three and
  the width of eleven?

39
Jennifers Question

• What steps were taken to normalize the
  Crutchfield database and what advantages did the
  normalization provide?

40
Brads Question

• What would be the SQL query used in Access to
  show all customers with vehicles made in 2000 or
  later?

41
Alans Question and Answer

• What does Crutchfield.com do to insure that they
  have all the necessary products?