Lecture 1 - Database management system

• A database is

  • Collection of organized data
  • Data has Various elements that can differ in type and size
  • allows end users to extract data
  • Can be used in different applications and departments
  • Can store a variety of file types - txt, mp4, PNG, MPG
• DBMS - Database management systems are the software that stores databases in order to extract and manipulate data.
• The main difference between a database and DBMS is that databases is just the physical data structure that's stored whereas DBMS can store/ house these databases in order to obtain new information. [via manipulation/ extraction/queries]
• Examples of databases would be

  • monthly expense trackers
  • asset tracker
  • EHR
• columns are referred as fields and they identify characteristics or describe the data in the table. [ attributes] ex- name, address, DOB, gender, etc.
• Primary key is an attribute (column) with unique value(s). It identifies one record from another.
• rows are called records and contain values for all columns within it. Ex- John Doe, Jane Smith, etc.
• Foreign keys are attributes that link tables together by referencing primary keys. They allow us to relate multiple tables together.
• You want to use a database over an excel/spreadsheets is that

  1. It can store a very large amount of data
  2. easy to maintain data
  3. avoids data redundancy
  4. has the ability to control user entry by making sure its valid for that data element
  5. Security
  6. Data relationships are defined
• Personal database level is anything with less than 50 user, can only be access in a single location, are simple, and doesn't emphasize security.
• enterprise database levels can be access by 50 or more users that can be access in multiple locations. They also are more complex, enforce security, and have data backup/redundancy.
• local databases are referred to "on premise" that require room and staff to maintain them. BUT they have faster speed, are harder to hack, and can be accessed offline.
• Cloud databases are "remote" which are easier to upgrade and cost less. BUT you have to trust vendors, no ownership of data, and cannot access without internet.
• A table is something you want to define/ represent (entity)
• index/constraint help enhance speed of data retrieval and maintains data integrity.