Database Database


Download 466 b.
bet7/19
Sana14.08.2018
Hajmi466 b.
1   2   3   4   5   6   7   8   9   10   ...   19

For Oracle and SQL Server, it is best to create a text file that contains all of the SQL statements to create the table.

  • For Oracle and SQL Server, it is best to create a text file that contains all of the SQL statements to create the table.

    • It is usually easier to modify the text table definition.
    • The text file can be used to recreate the tables for backup or transfer to another system.
    • To make major modifications to the tables, you usually create a new table, then copy the data from the old table, then delete the old table and rename the new one. It is much easier to create the new table using the text file definition.
    • Be sure to specify Primary Key and Foreign Key constraints.
    • Be sure to create tables in the correct order—any table that appears in a Foreign Key constraint must first be created. For example, create Customer before creating Order.
    • In Oracle, to substantially improve performance, issue the following command once all tables have been created:


Estimate the total size of the database.

  • Estimate the total size of the database.

    • Current.
    • Future growth.
    • Guide for hardware and software purchases.
  • For each table.

    • Use data types to estimate the number of bytes used for each row.
    • Multiply by the estimated number of rows.
  • Add the value for each table to get the total size.



Business rules

  • Business rules

    • Three year retention.
    • 1000 customers.
    • Average 10 orders per customer per year.
    • Average 5 items per order.
























Natural languages (English) are too vague

  • Natural languages (English) are too vague

  • We need a query system with more structure

  • We need a standardized system so users and developers can learn one method that works on any (most) systems.

    • Query By Example (QBE)
    • SQL


What output do you want to see?

  • What output do you want to see?

  • What do you already know (or what constraints are given)?

  • What tables are involved?

  • How are the tables joined together?





Single table

  • Single table

  • Constraints

  • Computations

  • Groups/Subtotals

  • Multiple Tables



List all animals with yellow in their color.

  • List all animals with yellow in their color.

  • List all dogs with yellow in their color born after 6/1/04.

  • List all merchandise for cats with a list price greater than $10.

  • List all dogs who are male and registered or who were born before 6/1/04 and have white in their color.

  • What is the average sale price of all animals?

  • What is the total cost we paid for all animals?

  • List the top 10 customers and total amount they spent.











List all dogs with yellow in their color born after 6/1/04.

  • List all dogs with yellow in their color born after 6/1/04.









Negation of clauses

  • Negation of clauses

    • Not (A And B) becomes Not A Or Not B
    • Not (A Or B) becomes Not A And Not B


List all dogs who are male and registered or who were born before 6/1/2004 and have white in their color.

  • List all dogs who are male and registered or who were born before 6/1/2004 and have white in their color.





















Do'stlaringiz bilan baham:
1   2   3   4   5   6   7   8   9   10   ...   19


Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©fayllar.org 2017
ma'muriyatiga murojaat qiling