Database Basics

1. Database Basics BCIS 3680 Enterprise Programming

2. Database Concepts • A database usually consists of a number of tables. • It’s often file(s) on a hard drive or other data storage devices. • A database management system (DBMS) is the software application that is used to manage databases. • MySQL is a DBMS.

3. Table • A structured list of data of a specific type. • It describes a certain entity with various attributes. • A table must have a unique name. • There is debate regarding whether the name should be in singular or plural form. • Forta uses plural nouns. • My convention is to use singular nouns because it make more sense when it comes to draw the Entity Relationship Diagram (ERD) for the database.

4. Elements in a Table • Each record in a database is stored as a row in one of the tables, e.g., a new customer is entered as a new row in the Customer table. • Vertically, a table is made up of columns. A column is also called a “field.” • Each column defines a certain attribute of a record, e.g., name, address, phone number, etc. • All rows in the table have the same number of columns. For each record, you may enter its attributes as defined by the various columns.

5. Schema • Schema stores the “meta-data” about the table. • It defines the datatype for the information to be stored in a column – text, numbers, currency values, even large blobs of binary information. • See Appendix D for a list of MySQLdatatypes. • Table and column names cannot be one of the SQL keywords (Appendix E).

6. Example of Schema

7. null • A special “value” in database. It means there is no entry in a particular field. • Regardless of the datatypefor the field, null doesn’t mean – • 0 • 0.0 • Empty string • Space • It just means, there is nothing there. • If you want to force the entry in a field, you set “no null” for the field in your schema.

8. Primary Key • Each row in a table must have a unique identifier so that it can be stored and retrieved correctly. • This is done by defining a field (or a combination of fields) as the primary key. • For each record, the value stored in the primary key field serves as its unique ID. • No two rows in the same table may have the same value for primary key. • A primary key field cannot be null. • No all unique values are good candidate for PK. For example, changeable values like email addresses are bad PKs.

9. “Sequel” to What? • Structured Query Language (SQL) is a language designed to communicate with databases. • There are minor syntax differences in implementations across different vendors (Oracle, Microsoft, IBM, MySQL AB, etc.) and even within the same vendor (Microsoft SQL Server vs. Access). • E.g., the semicolon is not always mandatory. • Syntax is not case sensitive. However, it is conventional to capitalize the keywords, e.g., SELECT… FROM… • It enhances readability of programming code and stored procedures.

10. Keywords and Clause SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50; • The keywords are SELECT, FROM, and WHERE. • The SELECTclause tells us that this is for data retrieval. • The FROMclause specifies which table to look for information. • The WHEREclause defines the criterion for retrieval.