Bklein7: Lecture notes from 10/1

2015-10-01T22:54:02Z

Lecture notes from 10/1

Bklein7: Added Lecture Notes from 9/29

2015-09-29T22:53:58Z

Added Lecture Notes from 9/29

New page

==Lecture on 9/29==
*'''Relational Databases'''- most common type of database
**[https://xmlpipedb.cs.lmu.edu/biodb/fall2015/index.php/PostgreSQL_Tutorial#Creating_a_Database PostgreSQL Tutorial]
**Databases History
***Prior- Network Databases; ended up getting blown out of the water and unified by the relational database
***Future- ''No SQL'' Databases
**Structure- series of tables
***''Tables'' are collections of rows that give specific instances of information that complies with an overall structure. The domain (set of accepted values) for each column is specified. Some possible domain options include marker, number, and text.
****The table is the standard unit of the relational database.
***Became popularized due to its strong mathematical foundations.
***A relational database is a collection of several tables. Coordinated information.
****The particular plan for a database that describes what's in it and where everything is located is reffered to as a ''schema''.
**Key- singular value that serves to identify the rest of the data in a particular row (e.g. id column)
***''Primary Key''- the chosen value that it is agreed upon for everyone to use as an identifier; typically easy or conveint
***''Foreign Key''- storing information in one table using the primary key of another table; primary use of keys is to connect tables
****foreign keys can link together dozens of tables in unique ways; establish many to many relationships (tables that connect other tables)
*[http://myweb.lmu.edu/dondi/share/db/sql.pdf SQL] (can use ''pgAdmin III'')
**2 Conceptual Parts
**#Data definition language (DDL)
**#Data manipulation language (DML)
**DDL Commands
***<code>create table</code>
****table name, column name & data type, column constraint, primary key, references, etc.
**In class example
***Create Database
***<code>create table movie (id int primary key, year int, title varchar)</code>
***(review)

← Older revision		Revision as of 22:54, 1 October 2015
Line 26:		Line 26:
	***<code>create table movie (id int primary key, year int, title varchar)</code>		***<code>create table movie (id int primary key, year int, title varchar)</code>
	***(review)		***(review)
		+	==Lecture on 10/1==
		+	*Live session of [https://xmlpipedb.cs.lmu.edu/biodb/fall2015/index.php/PostgreSQL_Tutorial PostgreSQL Tutorial].
		+	*4 fundamental functions of a database
		+	*#''C''reate
		+	#insert...
		+	*#''R''etrieve
		+	#select...
		+	#select * from movie
		+	#select (column) from movie where (ex. year > 1990) order by (column)
		+	#~ for sed-like commands
		+	#desc= descending order
		+	# *=show me everything
		+	#select count(*) from...
		+	#select year, count(*) from movie where year > 1985 and year <1996 group by year
		+	*#''U''pdate
		+	#update...set...where
		+	#update (table) set (column = new value) where (id = #)
		+	*#''D''elete
		+	#delete from...where
		+	#delete from movie (where...)
		+	*Syntax Notes
		+	**Any apostrophes in data needs to be typed in as two apostrophes
		+	*<code>cat movie_titles.txt \| sed "s/'/''/g" \| sed "s/$/');/g" \| sed "s/,/,'/2" \| sed "s/^/insert into movie (id, year, title) values (/g"</code>
		+	*Individual Web Directory
		+	**Can be found at http://my.cs.lmu.edu/~bklein7/
		+	**Can use browser to get files placed in this "mini web page"
		+	**Placing files here
		+	*** > symbol places a command output into a file
		+	***personal directory: bklein7/public_html
		+	***command: > bklein7/public_html?movie.sql.txt
		+	*Creating a new table with a reference to a foreign key
		+	**add reference table(column) where you would place a primary key term when creating a table
		+	*select from movie, performer where movie.id=performer.movie_id

Talk:Bklein7 Week 6 - Revision history

Bklein7: Lecture notes from 10/1

Bklein7: Added Lecture Notes from 9/29