CSCI_4400_NOTES_2_26_2019.md

CSCI_4400_NOTES_2_26_2019.md

Venn Diagrams

• Union
  • A or B
• Interesction
  • A and B
• Difference
  • A-B
    • all of B, except anything in A
  • B-A
    • all of B, except anything in A

Relation Set ops

• Multiply
  • based on rows
  • 8 row by 3 row gives 24 rows
• Divide
  • Filter table by a column of similar name
• Join
• Data dictionary
  • desc of all data tabls and their data types
• System Catalog
  • desc all objs within the DB
• Homonyms and synonyms should be avoided
  • Homonnyms - sound the same
    • C_name is customer or currency
  • Synonym - diff names for same attr
    • car and automobile
• Relationships within the Relational Database
  • One-to-many (1:M)
    • Normal
  • One-to-One (1:1)
    • One entity can be related to only one entity
  • Many-to-Many (M:M)

Entity Relationships

Codd's Realational Rules

• Information - all info must be logically reped column values and rows
• Guaranteed access - all info is reachable
• Systematic treatment of NULL - Nulls must be handled independent of data type
• Dynamic online catalog - metadata must be stored and managed as normal data. be under one language on control
• View catalog - any view that is updatable
• High-level insert, update, and delete - support the given details
• Phyiscal data Independence - env makes no diff on data
• logical data IND - apps are unaffected
• Integrity IND - all constraints must be definable
• Distribution IND - end user is affected by data location
• Nonsubversion - if support low-level access, the user must not get be integrity constraints
• Rule Zero - All preceding rules are based on notion of relational, a database must use this relation facility for management

Date and Time data types

Query: before a certain date

SELECT
  *
FROM
  Orders
WHERE
  OrderDate < '1996-11-01';

Query: before 1996

SELECT
  *
FROM
  Orders
WHERE
  OrderDate BETWEEN '1996-01-01' AND '1996-12-31';

Query: before 1996 new way

SELECT
  *
FROM
  Orders
WHERE
  YEAR(OrderDate) = 1996;

Query: and month

SELECT
  *
FROM
  Orders
WHERE
  YEAR(OrderDate) = 1996 and MONTH(OrderDate) = 5;

Query: tally up the freight

SELECT
  SUM(Frieght)
FROM
  

Query: most recent

SELECT
  TOP(1) OrderDate
FROM
  ORDERS
ORDER BY
  OrderDate;

1996-07-04

Query: least recent

SELECT
  TOP(1) OrderDate
FROM
  ORDERS
ORDER BY
  OrderDate;

1998-05-06

Query: avg feight

SELECT
  AVG(Freight)
FROM
  ORDERS;

Query: avg freight of 1996

SELECT
  AVG(Freight)
FROM
  ORDERS
WHERE
  YEAR(OrderDate) = 1996;

Query: avg freight of 20 years of data

SELECT
  AVG(Freight)
FROM
  ORDERS
GROUP BY
  YEAR(OrderDate);

NOTE: OUT OF ORDER DATA

Query: avg freight of 20 years of data ordered

SELECT
  AVG(Freight)
FROM
  Orders
GROUP BY
  YEAR(OrderDate)
ORDER BY
  YEAR(OrderDate);

Query: avg freight of 20 years of data ordered with year

SELECT
  YEAR(OrderDate), AVG(Freight)
FROM
  Orders
GROUP BY
  YEAR(OrderDate)
ORDER BY
  YEAR(OrderDate);

Query: avg freight of 20 years of data ordered with year with column names

SELECT
  YEAR(OrderDate) as 'Year Ordered', AVG(Freight) as 'Average Freight'
FROM
  Orders
GROUP BY
  YEAR(OrderDate)
ORDER BY
  YEAR(OrderDate);

Query: Month avg freight

SELECT
  MONTH(OrderDate) as 'Month Ordered', AVG(Freight) as 'Average Freight'
FROM
  Orders
GROUP BY
  MONTH(OrderDate)
ORDER BY
  MONTH(OrderDate);