ERD and SQL

What can SQL do?

-execute queries against a database -retrieve data from a database -insert records in a database -update records in a database -delete records from a database -create new databases -create new tables in a database -create stored procedures in a database -create views in a database -set permissions on tables, procedures, and views

ERD notation: cardinaility

-number of occurrences in one entity, which is associated w number of occurrences in another - one to one -one to many -many to many

Why do we use ERD

-to define terms related to entity relationship modeling -preview of how all ur tables should connect and what fields in each table -helps u describe entities, attributes, relationships - communicate w logical structure of the database to users

Full outer join

A matching field between two tables -full outer join product on market.prod_id=product.prod_id

Know how to create an ERD for this scenario following ERD steps

A student enrolls in courses. A student must be assigned to at least one professor. To maintain instruction quality, a professor can delivery only one course

COUNT

COUNT(*) Returns number of rows that matches a specified criteria

SQL data types

Char, varchar, INT

foreign key (FK)

Column(s) that refers/links to another table's PK -est. relationship w two tables to indicate they're associated w each other BASICALLY PK AND FK IS A COMMON/SAME SHARED FIELD BETWEEN TWO TABLES (ORDERS PK is Order_id connected to SHIPPING FK is Order_id)

AND

Combined with the WHERE clause; can be AND, OR, NOT

Steps to create ERD

Entity Identification> Relationship Identification> Cardinality Identification> Identify Attributes> Create ERD

What is an ERD

Entity Relationship Diagram: displays relationships of an entity set stored in a database - helps u explain the logical structure of databases

WHERE

Filter records; If looking for how many orders had critical order priority: WHERE order_priority= 'CRITICAL' (place in quotations) If wanting data between specific dates: WHERE orders.order_date between '01-AUG-2013' and '15-AUG-2013'

Common cardinal relationships

One to one One to many Many to many

What does the circle represent in a cardinal

Optional

AS clause

Rename attributes (AKA) -COUNT(*) as count; the asterick* selects everything -SUM(sales) as sales

SUM

SUM() total sum of column -how many sales were made in COMPUTERS? SUM(sales) as sales

SELECT

Select data from database (fields like Prod_category, prod_sub_category)

Between

Selections values within given range -can be numbers, texts, dates

ERD notation: types

Type of data in corresponding field; far right column associated with corresponding field - Order_date's type is the "DATE"

FROM

Which entity/chart the data is coming from

ERD notation: Entity Attribute

a property or characteristic that describes an entity AKA columns (lists key/field/type columns)

ORDER BY

ordering the output of a SELECT query (for example, in ascending or descending order). ASC DESC

What is an entity? (ERD notation)

table (names the chart) -must have an attribute and unique key

primary key (PK)

unique ID Each table in a database has one PK - for example, for an Order entity (table), its PK is Order_id Product table PK is Prod_id -it connects to an FK from another table

ON

Joins columns w diff names (full outer join between two entities) Full outer join product on market - the ON is joining the product and market table

What does the one line represent in a cardinal

Mandatory