Vocabulary
- Attribute: in an E-R model, attributes are further information about the entity. For example, for the entity author, we could have attributes: first name, last name, nationality, genre, literary movement, etc.
- Connectivity: in an E-R model, denotes whether the relationship between two entities is one to one, one to many, or many to many.
- Data modeling: identifying entities, attributes, and relationships
- Example notation from VTC:
- Database: an organized collection of data
- Database management system: software that helps create, maintain, and provide user access to a database
- Users can query the database with the software to retrieve results
- Relational database management system- uses tables to store data and other tables to relate data to each other
- MySQL- most common RDMS
- Entity: In an E-R model, the entity is the thing or object involved in whatever process the model is representing (e.g. book, author)
- Foreign key: a key introduced into one table in an E-R model in order to identify another piece of data in another table
- Intersection entity: In an E-R model, bridges a many to many entity relationship
- Keys: in RDMS, identifies data in a particular way (see primary key, surrogate key, unique key)
- Middleware: software that bridges two otherwise incompatible software, such as middleware that imports XML (a hierarchical database) into a SQL RDMS
- Normalization: breaking down complex relationships in the E-R database to improve efficiency
- 1st normal form- data is broken up to smallest units possible and no data fields are repeated in the same row
- 2nd normal form- each piece of data in a row must refer to the primary key in that row; if not, that data should go in a different table
- 3rd normal form- in tables that have a single field as their primary key, each piece of data in that table must refer to the primary key
- Primary key: uniquely identifies a row in a table
- Relational database:"presents to the user a view of the database as a collection of entities, represented as tables that consist of attributes which can be linked by relations" (Fulton)
- Relationship: in an E-R model, the relationship is the association of entities.
- SQL: Structured language query that uses specific, particular vocabulary and syntax to perform the query.
- Example:
- The most common SQL command is the QUERY which retrieves data from the database in the way specified
- SELECT = the main query command
- Also FROM, WHERE, GROUP BY, HAVING, ORDER BY
- Surrogate key: a unique key that does not derive from the data itself
- Unique key: identifies a row in each table in an RDMS
My SQL commands & clauses
- alter NameofTable...
- add NewNameofColumn ColumnType default='XX' = adds a new column, in this case with a default value
- add index(NameofCol); = creates an index for the column specified
- change NameofColumn NewNameofColumn; = renames column numbers
- rename NewTableName = changes name of table
- create database NameofDB; = creates a database
- delete from NameofTable where ColumnX='y'; = deletes a row (or rows) containing the value is 'y'
- drop table NameofTable; = deletes the table
- insert into NameofTable values ('value 1', 'value 2'); = puts data into a row of the table (the # if values = the number of columns)
- insert into NameofTable (NameofColumn) values ('abc'),('def'),('ghi');
- not null = used in a command line, doesn't allow a value to be null in the DB
- primary key(NameofCol) = makes that column the primary key for each row
- autoincrement = the computer automatically inserts numbers for us
- select * from NameofTable; = shows data from that table (* means all columns, could also put names of columns with comma in between)
- show columns from NameofTable; = shows a list of & infor about the columns in the specified table
- show tables; = displays the tables in the DB specified
- update NameofTable set NameofColumnA='x' where NameofColumnB='y' = edits the data requested, for example, update fruits set color='blue' where name='apple';
- use NameofDB; = set the specific DB to be used
- where = clause that specifies which row you want the information from, as in select colx, coly where colx = z, or select coly where coly like '%zee%'.
