130 likes | 305 Views
Database Basics. BCIS 3680 Enterprise Programming. 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.
E N D
Database Basics BCIS 3680 Enterprise Programming
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.
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.
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.
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).
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.
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.
“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.
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.