COSC 3337 Database Management Systems Class Syllabus Class Schedule

1. COSC 3337 Database Management Systems Class Syllabus Class Schedule Note Homework and Lab assignmentsNote due dates • Database Designer • Database ProgrammerDatabase Administrator • System Administrator

2. Vocabulary is a critical piece of any discussion. Articulating ideas/thoughts can be the bane of your success. Don’t be afraid of making a mistake or expressing an idea incorrectly 6 times. Database Theory and Applications DesignImplement designs

3. What is a database and what is it used for? • Database Management System (DBMS) is the name of the system • 'database' is typically used loosely to mean DBMS • - an organized data repository • - Computer software that manages the repository • - Provides Security – user levels, encryption      Data integrity – tools to assist      Reporting/Summary capabilities Maintenance capabilites – tools to assist      Backup and recovery capabilities      Auditing capabilities Scalability Portability

4. Databases support company processes, web applications, regulatory requirements and virtually everything done electronically. COSC 3337 • Learn RDBMS design tools • Install and administer an RDBMS • Write SQL to implement/maintain an RDBMS • Create objects, add/update/delete records, users • Implement security, transaction processing, backup/recovery • Implement a project

5. DBMS Software - access - security security schemes - update - backup - recovery • data dictionary • metadata Database Files (physical files) Database Management System Application Components Database Server

6. DBMSsBig Boys MySQL Oracle DB2 PostgreSQL Adabas MS SQLServer Small Players Access FileMaker Pro DBMS Software - access - security security schemes - update - backup - recovery data dictionary metadata Database Files (physical files) Database Management System Application Components Database Server

7. Application Development Software DBMS Software - access - security security schemes - update - backup - recovery data dictionary metadata Database Files (physical files) Database Management System Application Components Database Server

8. Application Development Software Java C++ Perl PHP … HTML, XHTML DBMS Software - access - security security schemes - update - backup - recovery data dictionary metadata Database Files (physical files) Database Management System Application Components Database Server

9. Application Development Software [connect] DBMS Software - access - security security schemes - update - backup - recovery data dictionary metadata Database Files (physical files) Database Management System Application Components Database Server db01.cs.stedwards.edu Web Server Application Application

10. How can you access data on the physical files that contain database records? COSC 3335 – they are just a bunch of spinning disks or SSDs that contain 0s and 1s. Just access the disk and read what you want. perl, prolog, asm, java, c++, ??

11. How can you access data on the physical files that contain database records? COSC 3335 – they are just a bunch of spinning disks or SSDs that contain 0s and 1s. Just access the disk and read what you want. perl, prolog, asm, java, c++, ?? SQL Structured Query Language No other way? SQL the same language for DB2, PostGreSQL, MySQL ??

12. What are our study materials?Online tutorialsClass notes Online articles Conceptual Logical Physical ER Diagrams E – Entity R – Relationship Discovering entities amid the attributes. Conceptual – don’t worry much about attributes or data types.

13. RDBMS – Relational Database Management System A DBMS that supports the relational model. Tables are in at least 3rd Normal Form and all relationships support referential integrity.

14. Relational Database A database that consists of files and relationships that adhere to a set of rules and provides * data integrity at the field, record, table, relationship and business levels * Logical and physical data independence * Data consistency and accuracy * Robust user interface with programming tools

15. Most detrimental result of improper database design - corrupt data Objectives of good design • Support for standard database inquiry and maintenance • Table and relationship structure normalized • Minimum data redundancy (eliminate??) • Data integrity features at all levels • Support for business rules of the organization • Scalability

16. Tables are 'Normalized' in order to ensure that the database structure provides data integrity, minimizes redundancy and supports referential integrity. Normalization involves designing tables according to rules. • 1st Normal Form • 2nd Normal Form • 3rd Normal Form • Examples of each follow.

17. Database vocabulary (we will come back to these often) Table – Entity or a person, place or thing Looks like a spreadsheet with headers Record – rows in a table, line Attribute or field– a characteristic of a record, columns in a table Atomic attribute – one that doesn’t have any subparts. FullName is not atomic Domain of an attribute – all permissible values See text / reference for data types

18. Database vocabulary Derived field – a field that can be derived from existing data in a database. Ex: age from birthdate. Usually don’t keep derived fields in database. Composite field – a field that can * and must? * be decomposed into atomic fields. Ex: name. Decompose to firstname lastname * must, meaning in a reasonable way Repeating field – a field that can have more than 1 value for a record. These are eliminated (resolved) in a relational database.

19. Database vocabulary Superkey– a field or fields that uniquely identify a record. Candidate key– a minimal superkey. No proper subset uniquely identifies a record. Primary key – a field (or fields) that uniquely describes a record, no parts of it uniquely describe a record, and it is not null (empty)note – no part of it is null. A primary key made up of several fields is a ‘composite primary key’ Foreign key – a field(s) in a table that is the primary key in another table.

20. Database vocabulary Partial Dependency: a non-key field depends on part of the primary key Transitive Dependency – field a depends on field b, field b depends on field c so field a depends on field c. If a, b and c are in the same table, they must be resolved.

21. Database vocabulary (relationships) • A. Cardinality– one-to-many, one-to-one, many-to-many. B. Optionality / Relationship participation Mandatory OptionalEach relationship involves 2 tables. In a one-to-many relationship, the “one” table is the parent table and the “many” table is the child table. M:M not acceptable in a RDBMS

22. Example: Let’s keep track of students in our class and their vehicles. Fly over and identify what you are keeping track of. Students? Make of vehicle? First name? If you identify attributes at the logical level, you are flying too low and will probably crash. What person/place/thing/concept can we identify?

23. Example: Let’s keep track of students in our class and their vehicles. Fly over and identify what you are keeping track of. Students? Make of vehicle? First name? If you identify attributes at the conceptual level, you are flying too low and will probably crash. What person/place/thing/concept can we identify?