1 / 10

Database Basics

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.

Download Presentation

Database Basics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

More Related