Database Mapping Process Transforming ERDRelational Schemas sextract relations sidentity a Primary Key (PK) > Add req. Foreign Keys (FK ) 1: M- PK in 'one side' FK in 'many side" M:Ms Associative Table w / 2 (or more ) FK s Transforming ERD into Relations Entity: New table for an entity. Each simple (atomic) attribute becomes an attribute of the table s NO spaces () Composite Atribute: Only the simple component attributes of composite attributes are included in table Adders in table be comes CustomerID | Name / Street | City Iprovince { }Multivalued Attributes create a new table to replace the multivalued attribute PK of this new tables PK of original table + FK & multivalued attribute CUSTOMER A CUSTOMER customer ID Name Customer 1 Name ( Address IPostal code 1 Address Postal Code EInteresty CUSTOMER INTEREST customer ID } interest 1:1 > PK of the table on mandatory side becomes a FK an optional side EMPLOYEE Employee ID Name DEPARTMENT Dep 10 Dep Name 01 EMPLOYEE Employee ID / Name DEPARTMENT Dep ID / Dep Name | EmployeeD
- ------ 1: M PK of one-side becomes FK of many-side EMPLOYEE Employee ID 11 Name DEPARTMENT 16 Dep ID Sep Name EMPLOYEE EmPID | Name DEPARTMENT DepID / Dep Name | Emp ID M:M New associative table w/ both the PK of the 2 tables Relationships Unary screate one table -> Assign PK to the table, then add the fk to the same table which references the PK values of that tables Recursive foreign Key EMPLOYEE Emplo Name EMPLOYEE Emp ID , Name, Manager () manages Recursive FR * Unary MIN relationship
SQL SQL= Structured Query Language database language designed for managing data in RDBMS - SQLis used for creating databases , adding, modifying of deleting table structures Interting, deleting, & modifying records in databases > Querying databases Categories ODDL , CREATE, ALTER, DROP 2 DML > INSERT, UPDATE, DELETE 3 DQL & SELECT Data Types CHAR (n) > string values of fixed length (n) VARCHAR (h) - string values of variable length (n= max size) NUMERIC (pis) , exact no. with a defined precision & scale DECIMAL (PIS) p = max total no. of digits s = man no. of digits after decimal INT sinteger , FLOAT >approx no. values DATE - date values TIME 1 time values Syntax > SQL commands are not case sensitive I can bewritten in one line of text send with a semicolon (;) CREATE table CREATE TABLE Fable Name ( Column Name! Data Type Default Column_Constraint Column Name 2 Data Type Default Column_Constraint Tabla Inaltra: )