Русский    English

Database "Computer firm"

The schema of the database consists of four tables (Figure 1.1):

  • Product(maker, model, type)
  • PC(code, model, speed, ram, hd, cd, price)
  • Laptop(code, model, speed, ram, hd, screen, price)
  • Printer(code, model, color, type, price)

In the Product table the data on the maker, model number and type of the product (PC, Laptop or Printer) is provided. It is supposed that in this table the model numbers are unique for all the producers and the types of the products. In PC table for each model number, designating PC, there are indicated processor speed (MHz), total volume of RAM (MByte), hard disk storage, hd (in GByte), compact disc drive speed - cd (e.g., '4х'), and the price. Laptop table is analogous to РС table apart from the fact, that instead of the speed of CD-drive the screen size (in inches) is indicated in it. In Printer table for each printer model it is indicated, whether it is a color printer ('y', for the color printer), printer type (Laser, Jet or Matrix), and price. 

Схема данных

Fig. 1.1. Schema of «Computer company» database

Additional information can be obtained from the logical schema of data shown in Fig. 1.1. Table of product types (PC, laptops and printers) contains the foreign key (model) to the Product table. One-to-many relationship means that in each of these tables the model, included into Product table, may be missing. On the other hand the model with one and the same number may be listed in such table several times, and sometimes even with fully identical technical characteristics, because the primary key in this case is the code column. The latter fact calls for additional explanation, as different people interpret the term model in a different way. Within the framework of this schema it is considered that the model is the combined result of the efforts of producer and of the use of a certain technology. E.g., identical models may be supplied with technologically identical memory devices but of different capacity, i.e., 60 and 80 GB. In particular, it means that in PC table there can be two PCs with identical model numbers, but at different prices.

In terms of the subject area this schema may mean that  Product table contains information on all the suppliers of this kind of products and models, supplied by them, and in the other tables there are models which are available (or offered for sale). Therefore it is quite possible, that the maker supplies the models which are not currently available.

Bookmark and Share
Развернуть всё
Свернуть всё


aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100