MySQL Universalclass Lesson1
https://www.youtube.com/watch?v=FhbCS6lx2wg&list=PLBlpUqEneF0-xZ1ctyLVqhwJyoQsyfOsO
objectives
- what is a database
- why use a database
- what’s in a database
- what does a database look like
- what is a DBMS
- how is data retrieved/updated
- history of SQL
- pros/cons of different DBMS
what is a database
- collection of files, organized in a logical way
- allow fast update/retrieval
- NOT a spreadsheet { many spreadsheets linked? with user access functions? }
- { ohh, auto-update for multi-user read/write }
why use a database
- allow multi-user read/write
- updated data available to all users (with access)
what’s in a database
- parts of a database
- tables (rows - records / columns - attributes)
- stored procedures
- views
- user logins/ roles
representations of a database
-
entity-relationship diagram (ERD)
-
table representation
DBMS
- data can be distributed, and have multiple copies
- DBMS handles merging for retrieving, and backing up
- DBMS also handles security
- need client tool (front end) to access DBMS server
- brands
- Oracle - the best, expensive
- MS-SQL server - fully integrates with Windows active directory domain (no need for separate login after logging into windows)
- mySQL
- postgreSQL
- SQLite - not scalable
- MS access - not robust, used in small applications
database design
logical design
- use diagrams to layout the database
physical design
- implement logical design in a DBMS
- some things you wanted in logical design might not be possible with a certain DBMS
SQL history
- relational, data tables are related by certain attributes
- SQL is a declarative language (not procedural)
- all you need is to tell DBMS WHAT to do, not step by step HOW to do it
mySQL pros/cons
- pros
- free, based on MS-SQL, large support base, works on several OS
- cons
- not scalable (if run out of space on one server, hard to add on a second server)
- Oracle bought mySQL, not upgrading mySQL
- not fully SQL compliant (some MS-SQL functions not found in mySQL)